SQL Server列车站间时长计算问题：查询返回空值需修正

现有SQL Server的train_schudle表，包含train_id（列车ID）、station_name（站点名称）、Reaching_Timing（到达时间）字段。需要在SELECT查询中新增duration列，计算同一列车相邻站点之间的到达时间差（单位：分钟）。但原查询返回的时长列结果异常，无法得到正确值。

原查询及问题 #

原查询语句 #

select t1.train_id, t1.Station_Name, t1.Reaching_Timing, DATEDIFF(MINUTE,t1.Reaching_Timing,t2.Reaching_Timing) 
from train_schudle t1 
left join train_schudle t2 on t1.train_id=t2.train_id 
group by t1.train_id, t1.Station_Name, t1.Reaching_Timing,t2.train_id, t2.Station_Name, t2.Reaching_Timing;

原查询结果 #

原查询问题分析 #

• 仅通过train_id关联表会导致同一列车的所有站点互相匹配，出现大量错误关联，最终DATEDIFF要么计算同一站点的时间差（结果为0），要么得到无意义的跨站点时间差
• 多余的GROUP BY子句未起到有效聚合作用，反而打乱了数据关联逻辑

修正后的查询方案 #

方案1：使用窗口函数（推荐） #

利用LEAD窗口函数直接获取同一列车的下一个站点到达时间，逻辑简洁高效：

SELECT 
    train_id,
    Station_Name,
    Reaching_Timing,
    DATEDIFF(MINUTE, Reaching_Timing, LEAD(Reaching_Timing) OVER (PARTITION BY train_id ORDER BY Reaching_Timing)) AS duration
FROM train_schudle;

• PARTITION BY train_id：按列车ID分组，确保仅在同一列车内计算相邻站点
• ORDER BY Reaching_Timing：按到达时间排序，保证站点顺序符合实际行驶顺序（若表中已有固定站点顺序字段，也可替换为该字段排序）
• LEAD(Reaching_Timing)：获取当前行的下一行到达时间，最后一个站点无后续站点，duration会返回NULL，符合业务逻辑

方案2：使用自连接（兼容旧版本SQL Server） #

若无法使用窗口函数，可通过给站点排序后自连接实现：

WITH ranked_stations AS (
    SELECT 
        train_id,
        Station_Name,
        Reaching_Timing,
        ROW_NUMBER() OVER (PARTITION BY train_id ORDER BY Reaching_Timing) AS station_rank
    FROM train_schudle
)
SELECT 
    t1.train_id,
    t1.Station_Name,
    t1.Reaching_Timing,
    DATEDIFF(MINUTE, t1.Reaching_Timing, t2.Reaching_Timing) AS duration
FROM ranked_stations t1
LEFT JOIN ranked_stations t2 
    ON t1.train_id = t2.train_id 
    AND t1.station_rank = t2.station_rank - 1;

• 先通过ROW_NUMBER()给每个列车的站点按到达时间排序，再通过排序序号关联上一个站点和下一个站点，计算时间差

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