SQL Server 2019中统计最新连续记录数（遇0停止计数）

要实现从按EDate倒序排列的记录中，统计从第一条开始到第一个seqRec=0之前的连续记录数（示例预期结果为6），可以通过以下两种简洁的方法实现：

方法一：利用行号定位第一个中断点 #

先给排序后的记录生成行号，找到第一个出现seqRec=0的行位置，以此计算连续记录数：

WITH SortedRecords AS (
    SELECT 
        EDate, EDateID, RID, rNum, prev_rNum,               
        CASE WHEN 
                    prev_rNum IS NULL
                    OR prev_rNum = rNum - 1
                    OR prev_rNum = rNum
                THEN 1 ELSE 0 END AS seqRec,
        -- 按最新到旧的顺序生成行号
        ROW_NUMBER() OVER(ORDER BY EDate DESC, EDateID DESC) AS rn
    FROM myTable
    WHERE RID = 7507
    GROUP BY EDate, EDateID, RID, rNum, prev_rNum
)
-- 计算连续数：第一个中断点的行号减1，若无中断则取总记录数
SELECT 
    COALESCE(MIN(rn) - 1, (SELECT COUNT(*) FROM SortedRecords)) AS LatestContinuousCount
FROM SortedRecords
WHERE seqRec = 0;

逻辑说明 #

• 先通过CTE SortedRecords 生成排序后的记录并添加行号，确保记录从最新到旧排列。
• 找到第一个seqRec=0的行号，用该行号减1即可得到前面连续的记录数；如果所有记录都是连续的（无seqRec=0），则返回总记录数。

方法二：利用累计中断次数筛选连续记录 #

通过累计中断次数（seqRec=0的次数），筛选出中断次数为0的记录并统计数量：

WITH SortedRecords AS (
    SELECT 
        EDate, EDateID, RID, rNum, prev_rNum,               
        CASE WHEN 
                    prev_rNum IS NULL
                    OR prev_rNum = rNum - 1
                    OR prev_rNum = rNum
                THEN 1 ELSE 0 END AS seqRec
    FROM myTable
    WHERE RID = 7507
    GROUP BY EDate, EDateID, RID, rNum, prev_rNum
    ORDER BY EDate DESC, EDateID DESC
),
RunningInterrupts AS (
    SELECT 
        *,
        -- 累计中断次数：seqRec=0时加1，否则加0
        SUM(1 - seqRec) OVER(ORDER BY EDate DESC, EDateID DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS InterruptCount
    FROM SortedRecords
)
-- 统计中断次数为0的记录数，即为最新连续记录数
SELECT COUNT(*) AS LatestContinuousCount
FROM RunningInterrupts
WHERE InterruptCount = 0;

逻辑说明 #

• 1 - seqRec 将seqRec=1转为0，seqRec=0转为1，通过窗口函数累计这个值得到InterruptCount。
• InterruptCount=0的记录表示从第一条开始到当前记录尚未遇到中断，统计这些记录的数量就是所需的最新连续记录数。

验证示例数据 #

针对你提供的示例数据，两种方法都会返回6，符合预期结果。

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