如何在SQL中按条件重置运行计数，从1开始并将前置行值设为0

原始数据表 #

No    Id    Value
1    100    1
2    100    0
3    100    1
4    100    2

1    101    1
2    101    2

1    102    1
2    102    0

计数规则 #

编写SQL查询实现以下运行计数逻辑：

• 当某行的Value为0时，该行的运行计数设为0，且同Id分组下该行之前的所有行运行计数都设为0
• 从Value=0的下一行开始，运行计数从1开始逐行递增
• 如果某个Id分组中没有Value=0的行，运行计数从1开始逐行递增

预期查询结果 #

No    Id    Value    Running Count
1    100    1       0
2    100    0       0
3    100    1       1
4    100    1       2

1    101    1       1
2    101    2       2

1    102    1       0
2    102    0       0

SQL解决方案 #

适用于支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等）：

WITH grouped_data AS (
    SELECT 
        No,
        Id,
        Value,
        -- 标记当前Id分组中最后一个Value=0的行的No值，无0则为0
        MAX(CASE WHEN Value = 0 THEN No ELSE 0 END) OVER (PARTITION BY Id) AS last_zero_no
    FROM your_table
)
SELECT 
    No,
    Id,
    Value,
    CASE
        -- 当前行在最后一个0的位置之前（含），计数为0
        WHEN No <= last_zero_no THEN 0
        -- 否则从1开始递增，等于当前行No减去最后一个0的行No
        ELSE No - last_zero_no
    END AS `Running Count`
FROM grouped_data
ORDER BY Id, No;

逻辑说明 #

1. 先用CTEgrouped_data为每个Id分组计算出最后一个Value=0的行的No值（last_zero_no），如果分组内没有0，则该值为0
2. 主查询中通过CASE判断：
   • 若当前行的No小于等于last_zero_no，运行计数设为0
   • 否则运行计数为当前行No减去last_zero_no，实现从1开始的递增计数

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