2153. The Number of Passengers in Each Bus II(当时不会)(NO)

最新推荐文章于 2025-08-22 11:49:22 发布

青色信笺

最新推荐文章于 2025-08-22 11:49:22 发布

阅读量452

点赞数

CC 4.0 BY-SA版权

分类专栏：力扣sql题文章标签：大数据

本文链接：https://blog.csdn.net/m0_69157845/article/details/125632702

力扣sql题专栏收录该内容

183 篇文章

订阅专栏

博客围绕SQL架构展开，描述了巴士和乘客在LeetCode车站的情况，当巴士到达且乘客到达时间合适、未搭乘其他巴士时，乘客会搭乘该巴士，且巴士有容量限制。要求编写SQL查询报告每辆巴士使用的乘客数量，并按特定顺序返回结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL架构

Table: Buses

+--------------+------+
| Column Name  | Type |
+--------------+------+
| bus_id       | int  |
| arrival_time | int  |
| capacity     | int  |
+--------------+------+
bus_id is the primary key column for this table.
Each row of this table contains information about the arrival time of a bus at the LeetCode station and its capacity (the number of empty seats it has).
No two buses will arrive at the same time and all bus capacities will be positive integers.

Table: Passengers

+--------------+------+
| Column Name  | Type |
+--------------+------+
| passenger_id | int  |
| arrival_time | int  |
+--------------+------+
passenger_id is the primary key column for this table.
Each row of this table contains information about the arrival time of a passenger at the LeetCode station.

Buses and passengers arrive at the LeetCode station. If a bus arrives at the station at a time tbus and a passenger arrived at a time tpassenger where tpassenger <= tbus and the passenger did not catch any bus, the passenger will use that bus. In addition, each bus has a capacity. If at the moment the bus arrives at the station there are more passengers waiting than its capacity capacity, only capacity passengers will use the bus.

Write an SQL query to report the number of users that used each bus.

Return the result table ordered by bus_id in ascending order.

The query result format is in the following example.

Example 1:

Input: 
Buses table:
+--------+--------------+----------+
| bus_id | arrival_time | capacity |
+--------+--------------+----------+
| 1      | 2            | 1        |
| 2      | 4            | 10       |
| 3      | 7            | 2        |
+--------+--------------+----------+
Passengers table:
+--------------+--------------+
| passenger_id | arrival_time |
+--------------+--------------+
| 11           | 1            |
| 12           | 1            |
| 13           | 5            |
| 14           | 6            |
| 15           | 7            |
+--------------+--------------+
Output: 
+--------+----------------+
| bus_id | passengers_cnt |
+--------+----------------+
| 1      | 1              |
| 2      | 1              |
| 3      | 2              |
+--------+----------------+
Explanation: 
- Passenger 11 arrives at time 1.
- Passenger 12 arrives at time 1.
- Bus 1 arrives at time 2 and collects passenger 11 as it has one empty seat.

- Bus 2 arrives at time 4 and collects passenger 12 as it has ten empty seats.

- Passenger 12 arrives at time 5.
- Passenger 13 arrives at time 6.
- Passenger 14 arrives at time 7.
- Bus 3 arrives at time 7 and collects passengers 12 and 13 as it has two empty seats.

with t1 as (
    select

 b.bus_id,
 b.capacity,
 
 
 count(p.passenger_id) cnt   #对passeng_id计数,找到 每辆车 最大 有多少人在等待 (不能 对 1 计数 有的车 是会没人等待的)
from
Buses b left join Passengers p
on b.arrival_time  >= p.arrival_time
group by b.bus_id,b.capacity
)

 select
 bus_id,cast(goPassenger as signed) passengers_cnt
  from
 (
 select
 bus_id,
 
 @goPassenger := if(capacity >= cnt - @gonePassenger ,cnt - @gonePassenger,capacity) goPassenger,  #可以上车的人数
 @gonePassenger :=  @goPassenger + @gonePassenger gonePassenger#已经走掉的人数
 from
 t1,(select @gonePassenger:=0,@goPassenger:=0) s1 #(初始化变量)
 ) s2
 order by bus_id