LeetCode（数据库）- 2142. The Number of Passengers in Each Bus I

-- 解决方案(1)
WITH t AS(SELECT * FROM Buses UNION ALL SELECT -1, -1),
t1 AS(SELECT *, ROW_NUMBER() OVER(ORDER BY arrival_time) rk FROM t),
t2 AS(SELECT ta.bus_id, ta.arrival_time low, tb.arrival_time high
FROM t1 ta JOIN t1 tb ON ta.rk + 1 = tb.rk),
t3 AS(SELECT high arrival_time, COUNT(*) cnt
FROM t2 JOIN Passengers p ON p.arrival_time > low AND p.arrival_time <= high
GROUP BY 1)
SELECT bus_id, IFNULL(cnt, 0) passengers_cnt
FROM Buses LEFT JOIN t3 USING(arrival_time)
ORDER BY 1
-- 解决方案(2)
select b.bus_id, count(t.passenger_id) passengers_cnt
from Buses b 
left join (
    select p.passenger_id, min(b.arrival_time) bus_arrival_time
    from Passengers p
    left join Buses b on b.arrival_time >= p.arrival_time
    group by p.passenger_id
) t on b.arrival_time = t.bus_arrival_time
group by b.bus_id
order by b.bus_id;