MySQL高阶2142-每辆车的乘客人数1

目录

题目

准备数据

​分析数据

总结

---

题目

公交车和乘客到达 LeetCode 站。如果一辆公交车在时间 tbus 到站，乘客在时间 tpassenger 到站，其中 tpassenger <= tbus，该乘客之前没有赶上任何公交车，则该乘客将搭乘该公交车。

编写一个 SQL 来查询使用每辆公交车的用户数量。

返回按 bus_id 升序排序 的结果表。

准备数据

Create table If Not Exists Buses (bus_id int, arrival_time int)
Create table If Not Exists Passengers (passenger_id int, arrival_time int)
    Truncate table Buses
    insert into Buses (bus_id, arrival_time) values ('1', '2')
    insert into Buses (bus_id, arrival_time) values ('2', '4')
    insert into Buses (bus_id, arrival_time) values ('3', '7')
    Truncate table Passengers
    insert into Passengers (passenger_id, arrival_time) values ('11', '1')
    insert into Passengers (passenger_id, arrival_time) values ('12', '5')
    insert into Passengers (passenger_id, arrival_time) values ('13', '6')
    insert into Passengers (passenger_id, arrival_time) values ('14', '7')

 buses表

passengers表

 分析数据

- 11 号乘客在时间 1 到达。
- 1 号公交车到达时间为 2，搭载 11 号乘客。

- 2 号公交车车在时间 4 到达，没有乘客。

- 12 号乘客在时间 5 到达。
- 13 号乘客在时间 6 到达。
- 14 号乘客在时间 7 到达。
- 3 号车在时间 7 到达，搭载 12、13、14 号乘客。

第一步:根据条件关联,并且筛选出最早到达的公交车

select passenger_id,min(b.arrival_time) as bus_time
from buses b join passengers p on b.arrival_time >= p.arrival_time
group by passenger_id;

第二步:将子查询再与buses表右关联,不符合条件的为ifnull

with t1 as (
    select passenger_id,min(b.arrival_time) as bus_time
    from buses b join passengers p on b.arrival_time >= p.arrival_time
    group by passenger_id
)select * from t1 right join buses on t1.bus_time = buses.arrival_time;

第三步:利用count函数统计个数

with t1 as (
    select passenger_id,min(b.arrival_time) as bus_time
    from buses b join passengers p on b.arrival_time >= p.arrival_time
    group by passenger_id
)select bus_id,count(bus_time) as passengers_cnt
 from t1 right join buses on t1.bus_time = buses.arrival_time
group by bus_id
order by bus_id;

总结

 count()函数可以统计ifnull值