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