2153. The Number of Passengers in Each Bus II

还是临时变量

with t as(
SELECT B.*,count(passenger_id) people
,sum(capacity) over(order by arrival_time rows unbounded preceding) seats
from Buses B LEFT JOIN Passengers P
ON B.arrival_time >= P.arrival_time 
group by bus_id)
SELECT bus_id, CONVERT(temp, UNSIGNED INTEGER) passengers_cnt from
(SELECT bus_id,@q:=@q+@p,
@p:=if(capacity >= people-@q,people-@q,capacity) temp
from t,(SELECT @p:=0,@q:=0) init) T
order by bus_id