SQL查询逻辑缺失排查：如何统计仅乘首趟适配巴士的乘客数

现有查询的问题 #

当前查询的核心问题是完全没有实现“每位乘客仅能分配给首趟符合条件巴士”的约束逻辑：

• 现有的LEFT JOIN会把所有满足「线路匹配+巴士时间≥乘客时间」的乘客，全部关联到对应的每一趟巴士上
• 这导致同一个乘客被重复统计到多趟符合条件的巴士中（比如乘客40会同时被算进巴士20、21、22的乘客数里），最终结果和预期不符。

修复思路 #

要实现需求，必须先为每个乘客锁定唯一的首趟符合条件巴士，再基于这个精准的匹配关系统计每趟巴士的乘客数。可以借助窗口函数来实现“分组取首条”的逻辑。

修复后的SQL代码 #

WITH passenger_first_bus AS (
    SELECT 
        p.id AS passenger_id,
        b.id AS bus_id,
        -- 按乘客分组，对匹配的巴士按时间升序排，首趟巴士行号为1
        ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY b.time ASC) AS rn
    FROM passengers p
    JOIN buses b 
        ON b.origin = p.origin 
        AND b.destination = p.destination 
        AND b.time >= p.time
    WHERE p.time <= '23:59'
)
SELECT 
    buses.id,
    COUNT(pfb.passenger_id) AS passengers_on_board
FROM buses
-- 只关联每个乘客对应的首趟巴士记录
LEFT JOIN passenger_first_bus pfb 
    ON buses.id = pfb.bus_id 
    AND pfb.rn = 1
GROUP BY buses.id
ORDER BY buses.id ASC;

逻辑说明 #

1. CTE子查询（passenger_first_bus）：
   • 先关联乘客和所有符合线路、时间要求的巴士
   • 用ROW_NUMBER()窗口函数，按乘客ID分组，对每个乘客匹配到的巴士按时间从早到晚排序，行号rn=1的记录就是该乘客能乘坐的首趟巴士
2. 主查询：
   • 从巴士表LEFT JOIN上述子查询，仅保留每个乘客对应的首趟巴士记录
   • 按巴士ID分组统计乘客数，没有匹配乘客的巴士会返回0，完全符合预期结果

验证示例数据 #

以示例数据为例：

• 乘客40匹配到巴士20、21、22，首趟是20，因此仅计入巴士20的乘客数
• 乘客41、42、43匹配到巴士21、22，首趟是21，因此这三位乘客全部计入巴士21
• 乘客44仅匹配到巴士22，计入巴士22
• 乘客1匹配到巴士30，计入巴士30
• 其余乘客无符合条件的巴士，对应巴士统计数为0
  最终结果完全和预期一致。

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