题解 | #各城市最大同时等车人数#

ATTENTION 两个坑： 1.sum加over函数累计和，一定要记得order by dt再加上 状态 desc 2.！！！union 会把分表中的重复行也删去，第二个例子有两个人同时打车，所以加了order_id区分

select city, max(t)  as mt
from
(select city, dt,sum(waiting) over(partition by city order by dt,waiting desc) as t
from
(select city,event_time as dt, 1 as waiting ,order_id
from
tb_get_car_record
 WHERE LEFT(event_time, 7) = '2021-10'
union
select  city,
       if(start_time is null,finish_time,start_time) as dt,
       -1 as waiting,tb_get_car_order.order_id
from
tb_get_car_order inner join tb_get_car_record using(order_id)
WHERE LEFT(event_time, 7) = '2021-10'
) base
)tmp
group by city
order by mt,city