SQL Server 2016 跨行DateDiff计算：求解通话间隔时间

嘿，刚好碰到过类似的需求！你说的跨行计算其实用窗口函数就能轻松搞定，我给你两种方案——一种是用专门的LAG()函数（SQL Server 2012及以上版本支持，最简洁高效），另一种是用你提到的ROW_NUMBER来关联，两种都给你讲清楚～

方案一：用LAG()窗口函数（推荐） #

LAG()是SQL Server专门用来获取当前行之前某一行数据的窗口函数，完美适配你的“取上一次通话结束时间”的需求。

假设你的通话表名为call_records，核心字段是start_time（datetime）、end_time（datetime）、user_name（string），直接用下面的SQL就能得到结果：

SELECT
    -- 按照你要的格式拼接结果
    CONCAT(
        FORMAT(start_time, 'yyyy-MM-dd HH:mm'),
        ' || ',
        user_name,
        ' || ',
        DATEDIFF(second, 
                 -- 取同一用户、上一次通话的结束时间
                 LAG(end_time) OVER (PARTITION BY user_name ORDER BY start_time),
                 start_time),
        's'
    ) AS interval_result
FROM call_records
-- 过滤掉第一次通话（因为没有上一次通话，间隔为空）
WHERE LAG(end_time) OVER (PARTITION BY user_name ORDER BY start_time) IS NOT NULL
ORDER BY user_name, start_time;

关键部分解释： #

• PARTITION BY user_name：确保只在同一个用户的通话记录里找前一次通话
• ORDER BY start_time：保证通话记录按开始时间排序，这样LAG取到的就是真正的“上一次”通话
• DATEDIFF(second, ...)：计算两次时间的秒数差，刚好符合你要的450s格式

方案二：用ROW_NUMBER()关联（兼容旧版本SQL Server） #

如果你用的是SQL Server 2008及更早版本（不支持LAG()），可以用ROW_NUMBER()给每个用户的通话编序号，再通过自连接找到上一次通话：

-- 先给每个用户的通话按时间编序号
WITH numbered_calls AS (
    SELECT
        start_time,
        end_time,
        user_name,
        ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY start_time) AS call_num
    FROM call_records
)
SELECT
    -- 拼接成你要的格式
    CONCAT(
        FORMAT(curr.start_time, 'yyyy-MM-dd HH:mm'),
        ' || ',
        curr.user_name,
        ' || ',
        DATEDIFF(second, prev.end_time, curr.start_time),
        's'
    ) AS interval_result
FROM numbered_calls curr
-- 关联上一次通话（序号小1）
LEFT JOIN numbered_calls prev
    ON curr.user_name = prev.user_name
    AND curr.call_num = prev.call_num + 1
-- 过滤第一次通话
WHERE prev.end_time IS NOT NULL
ORDER BY curr.user_name, curr.start_time;

这个方法的思路是：先给每个用户的通话按时间排号，然后把当前通话（curr）和上一次通话（prev，序号+1）关联起来，再计算时间差。

两种方案都能输出你要的格式，比如：2018-03-23 10:00 || John Smith || 450s

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