SQL Server中如何根据Score条件生成New Sequence列

我来帮你搞定这个序列生成的需求！先明确下核心要求：每当Score为1时，New Sequence重置为0；之后连续的Score为0的行，从1开始依次递增，直到下一个Score=1的行出现，再次重置为0。

先说说你尝试的代码问题：嵌套的CASE语句加上SUM窗口函数的组合逻辑有点混乱，没有正确把每个Score=1的行作为分组起点，导致序列计算出错。

下面是正确的实现方式，逻辑清晰且高效：

SELECT 
    MonthlyDate,
    Score,
    CASE 
        WHEN Score = 1 THEN 0
        ELSE ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY MonthlyDate) - 1
    END AS [New Sequence]
FROM (
    -- 先生成分组ID：每遇到一个Score=1，分组ID就加1，把连续的序列归为一组
    SELECT 
        MonthlyDate,
        Score,
        SUM(Score) OVER (ORDER BY MonthlyDate ROWS UNBOUNDED PRECEDING) AS GroupId
    FROM YourTableName -- 替换成你的实际表名
) AS GroupedData
ORDER BY MonthlyDate;

代码原理解释： #

1. 子查询生成分组ID：SUM(Score) OVER (ORDER BY MonthlyDate ROWS UNBOUNDED PRECEDING)会对Score进行累计求和。每遇到一个Score=1，累计值就会加1，这样每个以Score=1开头的连续行（包括1本身和后面的所有0）都会被分到同一个GroupId下。
2. 外层计算序列：
   • 当Score=1时，直接返回0，完全符合需求；
   • 当Score=0时，在同一个GroupId内用ROW_NUMBER()按日期排序，然后减1（因为ROW_NUMBER默认从1开始，减1后第一个0对应的序列就是1，第二个是2，以此类推）。

用你给出的示例数据测试这段代码，会得到完全符合预期的结果：

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