SQL Server中基于CarDetails表计算车辆停车时长的技术问询

嘿，我来帮你搞定这个车辆停留时长统计的问题！你的需求是按车辆和日期汇总每天的总停留时间，对吧？先看看你的数据结构，每辆车的进出是成对出现的（in之后跟着out），所以核心是先把每一次的进、出时间配对，再计算单次时长，最后按天汇总。

正确的SQL查询语句 #

WITH CarInOutPairs AS (
    -- 第一步：为每一条记录匹配同辆车的下一条记录时间（即对应的out时间）
    SELECT 
        carid,
        DateTimeDetail AS InTime,
        LEAD(DateTimeDetail) OVER (PARTITION BY carid ORDER BY DateTimeDetail) AS OutTime,
        Flag,
        CAST(DateTimeDetail AS DATE) AS StayDate -- 提取日期用于分组
    FROM dbo.CarDetails
),
SingleStayDuration AS (
    -- 第二步：只保留进入记录，计算单次停留的秒数
    SELECT 
        carid,
        StayDate,
        DATEDIFF(SECOND, InTime, OutTime) AS DurationSeconds
    FROM CarInOutPairs
    WHERE Flag = 'in'
      AND OutTime IS NOT NULL -- 排除还未驶出的车辆记录
)
-- 第三步：按车辆和日期汇总，将总秒数格式化为hh:mm:ss
SELECT 
    carid,
    CONVERT(VARCHAR, StayDate, 23) AS DateTimeDetails,
    CONVERT(VARCHAR, DATEADD(SECOND, SUM(DurationSeconds), 0), 108) AS [Totaltime(hh:mm:ss)]
FROM SingleStayDuration
GROUP BY carid, StayDate
ORDER BY carid, StayDate;

逻辑解释 #

1. 配对进出时间：用LEAD()窗口函数，按carid分组、DateTimeDetail排序，自动为每条in记录匹配后续的out时间，这样就把每次进出的时间对应起来了。
2. 计算单次停留时长：筛选出Flag='in'的记录，用DATEDIFF(SECOND, ...)计算每次停留的总秒数，同时排除没有对应out时间的记录（比如车辆还在停车场的情况）。
3. 按天汇总并格式化：按车辆ID和日期分组求和总秒数，再用DATEADD把秒数转换成时间，最后用CONVERT的108格式输出hh:mm:ss的时长。

测试结果 #

用你提供的测试数据运行这个SQL，会得到和你期望几乎一致的结果（注：你给出的car2在2019-01-20的期望时长05:49:40可能是笔误，实际计算应为05:40:00，对应20:30:10到次日02:10:10的时间差）。

为什么你的原SQL不对？ #

你之前的查询用了表自连接，但没有限定in和out的配对逻辑，导致计算的是所有时间点之间的差值总和，这显然不符合实际的停留时长统计逻辑，所以结果会出错。

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