SQL Server2008中列值为1或日期变更时重置Row_Number()

问题背景 #

现有数据表结构如下：

需要新增row_number列，重置规则：

• 当Condition字段值为1时，行号从1重新开始计数
• 当Date字段日期发生变化时，行号从1重新开始计数

期望输出结果：

环境限制为SQL Server 2008，无法使用LAG()、带ORDER BY的SUM()窗口函数等高版本特性。

实现思路 #

1. 首先给所有行分配全局连续行号，锚定行的先后顺序，解决关系表无序的问题
2. 识别所有重置点（首行、日期切换行、Condition=1的行），给每一行计算所属分组ID：每遇到一个重置点，分组ID+1
3. 按分组ID分区，按全局行号排序生成最终的连续行号

完整实现代码 #

WITH Step1 AS (
    -- 生成全局连续序号，锚定行顺序
    -- 如果原表有自增ID、创建时间等可明确排序的字段，可替换ORDER BY后的(SELECT 0)，性能更优
    SELECT 
        Date,
        Condition,
        ROW_NUMBER() OVER (ORDER BY Date, (SELECT 0)) AS GlobalRN
    FROM @myTablevariable
),
Step2 AS (
    -- 计算每个行所属的分组ID
    SELECT 
        Date,
        Condition,
        GlobalRN,
        (
            SELECT COUNT(1)
            FROM Step1 b
            WHERE b.GlobalRN <= a.GlobalRN
            AND (
                b.GlobalRN = 1 -- 首行为第一个分组起点
                OR b.Date <> (SELECT c.Date FROM Step1 c WHERE c.GlobalRN = b.GlobalRN - 1) -- 与上一行日期不同，为新分组起点
                OR b.Condition = 1 -- 当前行Condition=1，为新分组起点
            )
        ) AS GroupID
    FROM Step1 a
)
-- 按分组生成最终行号
SELECT 
    Date,
    Condition,
    ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY GlobalRN) AS row_number
FROM Step2
ORDER BY GlobalRN;

说明 #

• 上述代码完全兼容SQL Server 2008环境，无高版本特性依赖
• 若数据量较大，建议给排序用到的字段建立索引，可显著提升关联子查询的执行效率
• 执行结果与预期完全一致，不会出现Condition=0时错误触发行号重置的问题

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