SQL Server中Row_Number()遇0值后重新从1计数的实现问询

你的核心需求是：当RecordingDurationSeconds>0（对应RecordingDurationSeconds_Yeni=1）的记录被Yeni=0的记录打断后，后续的Yeni=1记录要重新从1开始计数，而非延续之前的序号。原查询的问题在于直接按Yeni值分区，导致所有Yeni=1的记录被归为同一组，无法实现分段计数。

实现方案 #

通过识别连续的Yeni=1分组，再针对每个分组单独生成序号，具体步骤如下：

1. 先计算每条记录的Yeni值，并获取前一行的Yeni值，用于判断是否是新分组的起点
2. 基于起点标记生成分组ID，每个连续的Yeni=1段对应唯一ID
3. 针对每个分组ID生成独立的序号

完整SQL代码 #

WITH CTE_WithFlags AS (
    SELECT
        *,
        -- 计算Yeni值
        CASE 
            WHEN RecordingDurationSeconds > 0 THEN 1 
            ELSE 0 
        END AS RecordingDurationSeconds_Yeni,
        -- 获取前一行的Yeni值（按联系人和日期分区、通话时间排序）
        LAG(CASE 
                WHEN RecordingDurationSeconds > 0 THEN 1 
                ELSE 0 
            END) OVER (PARTITION BY ContactCode, CAST(Calldate AS date) ORDER BY CallDate) AS Prev_Yeni
    FROM #tbl1
),
CTE_WithGroups AS (
    SELECT
        *,
        -- 生成分组ID：遇到Yeni=1且前一行不是1时，标记为新分组并累计计数
        SUM(CASE 
                WHEN RecordingDurationSeconds_Yeni = 1 AND (Prev_Yeni IS NULL OR Prev_Yeni != 1) 
                THEN 1 
                ELSE 0 
            END) OVER (PARTITION BY ContactCode, CAST(Calldate AS date) ORDER BY CallDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupID
    FROM CTE_WithFlags
)
SELECT
    *,
    -- 仅对Yeni=1的记录生成序号，Yeni=0的记录可设为0或NULL
    CASE 
        WHEN RecordingDurationSeconds_Yeni = 1 
        THEN ROW_NUMBER() OVER (PARTITION BY ContactCode, CAST(Calldate AS date), GroupID ORDER BY CallDate) 
        ELSE 0 
    END AS RowT
FROM CTE_WithGroups
ORDER BY ContactCode, CallDate;

代码解释 #

• CTE_WithFlags：完成基础值计算，通过LAG函数获取上一条记录的Yeni值，为判断分组起点做准备。
• CTE_WithGroups：用SUM累计窗口函数生成分组ID，每当遇到Yeni=1且前一行不是1的情况，就给分组ID加1，确保每个连续的Yeni=1段拥有独立的分组标识。
• 最终查询：在ContactCode、日期、分组ID的分区内生成RowT，实现分段计数；Yeni=0的记录可根据需求设置为0或NULL。

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