题目地址

https://leetcode.cn/problems/the-number-of-passengers-in-each-bus-i/description/

思路

将所有关键时刻作为tick。（同一时刻车和人同时到，默认人在车前到）
之后按照tick升序，使用MySQL用户变量编程完成计算逻辑。
输出结果。

代码

with ticks as(
    select arrival_time as tick,
    bus_id,
    null as passenger_id,
    "bus" as mytype
    from Buses 
    union 
    select arrival_time as tick,
    null as bus_id,
    passenger_id,
    "passenger" as mytype
    from Passengers
)
,
t1 as(
    select * ,
    row_number() over(order by  tick asc, field(mytype, "passenger","bus")) as rn
    from ticks
)
,
t2  AS (
    SELECT
        #------------只需要修改里面的逻辑就行。注意语句是“顺序执行”的-------------------------------
        *,

        (case when mytype="bus" then @station_passenger_number 
          when mytype="passenger" then @station_passenger_number := @station_passenger_number+1
        end) AS station_passenger_number,
        (case when mytype="bus" then @station_passenger_number := 0 
          when mytype="passenger" then @station_passenger_number := @station_passenger_number end )
        #-------------只需要修改里面的逻辑就行。注意语句是“顺序执行”的------------------------------
    FROM
        (SELECT @station_passenger_number := 0 ) vars,
        (SELECT * FROM t1 ORDER BY rn  asc ) ordered_ticks
)

select bus_id, cast(station_passenger_number as unsigned int) as passengers_cnt
from t2
where mytype="bus"
order by bus_id asc