获取每辆巴士唯一ID及对应登车唯一乘客数的SQL实现

需求 #

获取每辆巴士的唯一ID，以及登上该巴士的唯一乘客数量，最终结果以<ul><li>格式展示。

现有尝试的SQL语句 #

with abc as 
(
select x.bus_id,x.pasenger_id,x.destination,x.origin,case when X.pasenger_time <=  X.bus_time then 1 else 0 end flag
from (
select b."id" as Bus_id,a."id" as  pasenger_id,
b.ORIGIN,
b.destination ,TO_CHAR ( TO_DATE ( a."time", 'HH24:MI' ), 'HH:MI AM') as pasenger_time,
TO_CHAR ( TO_DATE ( b."time", 'HH24:MI' ), 'HH:MI AM') as bus_time
from buses b cross join
passengers a  WHERE 
b.ORIGIN=a.ORIGIN and
b.destination=a.destination 
--where 
--a."id"=b."id" and 
-- TO_CHAR ( TO_DATE ( b."time", 'HH24:MI' ), 'HH:MI AM') <= TO_CHAR ( TO_DATE ( a."time", 'HH24:MI' ), 'HH:MI AM')
order by pasenger_id asc,a."time") X  
) 
select * from abc where flag='1';


select b."id" as buses_id ,b."time"as buses_time  ,b.origin as buses_origin ,b.destination as buses_destination, 
 p."id" as passengers_id ,p."time"as passengers_time  ,p.origin as passengers_origin ,p.destination as passengers_destination
from buses b cross join  
passengers p where 
b.origin=p.origin and
b.destination=p.destination and 
TO_CHAR ( TO_DATE ( p."time", 'HH24:MI' ), 'HH:MI AM') <= TO_CHAR ( TO_DATE ( b."time", 'HH24:MI' ), 'HH:MI AM');



select b."id" as buses_id ,b."time" as buses_time  ,b.origin as buses_origin ,b.destination as buses_destination, 
 p."id" as passengers_id ,p."time"as passengers_time  ,p.origin as passengers_origin ,p.destination as passengers_destination
from buses b , passengers p where 
b.origin=p.origin and
b.destination=p.destination and 
TO_CHAR ( TO_DATE ( p."time", 'HH24:MI' ), 'HH:MI AM') <= TO_CHAR ( TO_DATE ( b."time", 'HH24:MI' ), 'HH:MI AM');

期望输出结果 #

原始期望格式：

id  count
10  0
20  1
21  3
22  1
30  1

转换为<ul><li>格式：

• 巴士ID：10，乘客数量：0
• 巴士ID：20，乘客数量：1
• 巴士ID：21，乘客数量：3
• 巴士ID：22，乘客数量：1
• 巴士ID：30，乘客数量：1

数据表结构 #

表1：Buses（巴士表） #

id  ORIGIN  DESTINATION time
10  Warsaw  Berlin      10:55
20  Berlin  Paris       6:20
21  Berlin  Paris       14:00
22  Berlin  Paris       21:40
30  Paris   Madrid      13:30

表2：Passenger（乘客表） #

id  ORIGIN  DESTINATION time
1   Paris   Madrid      13:30
2   Paris   Madrid      13:31
10  Warsaw  Paris       10:00
11  Warsaw  Berlin      22:31
40  Berlin  Paris       6:15
41  Berlin  Paris       6:50
42  Berlin  Paris       7:12
43  Berlin  Paris       12:03
44  Berlin  Paris       20:00

正确SQL逻辑实现 #

现有尝试的SQL存在两个核心问题：一是用字符串格式比较时间易出错，二是未按巴士分组统计且遗漏了无乘客的巴士。以下是修正后的SQL：

SELECT 
    b.id AS id,
    COUNT(DISTINCT p.id) AS count
FROM buses b
LEFT JOIN passengers p
    ON b.ORIGIN = p.ORIGIN
    AND b.DESTINATION = p.DESTINATION
    -- 直接转换为时间类型对比，避免字符串格式错误
    AND TO_DATE(p."time", 'HH24:MI') <= TO_DATE(b."time", 'HH24:MI')
GROUP BY b.id
ORDER BY b.id;

逻辑说明 #

1. 使用LEFT JOIN保证所有巴士都被统计，包括没有匹配乘客的巴士（乘客数为0）。
2. 时间直接转换为日期类型后对比，规避AM/PM字符串格式带来的比较逻辑错误。
3. 用COUNT(DISTINCT p.id)统计每辆巴士的唯一乘客数，防止重复计数。
4. 按巴士ID分组，最终按ID排序输出结果。

内容的提问来源于stack exchange，提问作者Deepak Kumar