在SQL Server 2008中基于SUB_ID计算非连续行的时间差

我有一张表，结构及数据如下：

我需要在SUB_ID不为空时，计算对应非连续行的时间差，最终要得到如下预期结果：

我当前使用的查询语句如下：

;WITH rows AS (
 SELECT *, ROW_NUMBER() OVER (ORDER BY TIME_OCCUR) AS rn
 FROM [PROC_MN].[dbo].[TBL_CURRENT_STATUS]
 WHERE SUB_ID IS NOT NULL
)
SELECT DATEDIFF(minute, mc.[TIME_OCCUR], mp.[TIME_OCCUR])
FROM rows mc
JOIN rows mp ON mc.rn = mp.rn - 1

咱们先理清楚问题核心：你当前的代码是用ROW_NUMBER()按时间排序关联连续行，但你的需求是通过SUB_ID精准匹配对应的目标行，这俩逻辑完全不匹配，所以得不到正确结果。

直接换个思路，咱们通过SUB_ID把当前行和它关联的行做匹配，然后计算时间差就行，具体SQL如下：

SELECT 
    t.ID,
    t.INDEXNO,
    t.PO_NO,
    t.ITEM_CD,
    t.PROC_NO,
    t.SEQ,
    t.STATUS,
    t.TIME_OCCUR,
    t.MC_NO,
    -- 当SUB_ID不为空时，计算当前行与关联行的分钟差
    CASE 
        WHEN t.SUB_ID IS NOT NULL THEN DATEDIFF(minute, t_sub.TIME_OCCUR, t.TIME_OCCUR)
        ELSE t.OPT_TIME
    END AS OPT_TIME,
    t.SUB_ID
FROM [PROC_MN].[dbo].[TBL_CURRENT_STATUS] t
-- 左连接自身表，用SUB_ID关联到对应的行
LEFT JOIN [PROC_MN].[dbo].[TBL_CURRENT_STATUS] t_sub 
    ON t.SUB_ID = t_sub.ID
ORDER BY t.ID DESC; -- 按ID降序排列，匹配你预期结果的顺序

逻辑拆解： #

1. 精准关联：用LEFT JOIN把表和自身关联，条件是当前行的SUB_ID等于关联行的ID，这样就能准确找到需要计算时间差的那一行，不会出现乱序匹配的问题。
2. 条件计算：用CASE语句判断SUB_ID是否为空，不为空时就用DATEDIFF(minute, 关联行的时间, 当前行的时间)算出分钟差，为空则保留原OPT_TIME的值。
3. 排序对齐：最后按ID降序排序，就能得到和你预期完全一致的结果。

你运行这个语句后，ID为74的行的OPT_TIME会算出2994分钟（刚好是21-03-18 8:53到23-03-18 10:47的分钟数），完全符合你的需求。

内容的提问来源于stack exchange，提问作者Cát Tường Vy