SQL Server：不使用计数器变量实现按值触发的分组ID递增

嘿，这个需求我刚好处理过！不用会话变量的话，用窗口函数的累计求和就能完美解决，而且写法更简洁、更符合现代SQL的最佳实践～

核心思路 #

每次遇到value=0的行时，就需要开启一个新分组。我们可以把这类行标记为1，其他行标记为0，然后对这个标记值做按行顺序的累计求和，最后调整初始值就能得到目标分组编号。

实现代码 #

假设你的表名为your_table，且id是按顺序递增的（保证分组的先后逻辑），SQL代码如下：

SELECT 
    id,
    value,
    -- 计算累计分组触发次数，加1确保初始分组从1开始
    SUM(CASE WHEN value = 0 THEN 1 ELSE 0 END) OVER (ORDER BY id) + 1 AS ideal_group
FROM your_table;

代码验证（匹配你的示例数据） #

我们把你给出的示例数据代入计算，每一行的分组逻辑如下：

• id=1: value=1 → 标记0 → 累计求和0 → 0+1=1 ✔️
• id=2: value=1 → 标记0 → 累计求和0 → 0+1=1 ✔️
• id=3: value=1 → 标记0 → 累计求和0 → 0+1=1 ✔️
• id=4: value=0 → 标记1 → 累计求和1 →1+1=2 ✔️
• id=5: value=1 → 标记0 → 累计求和1 →1+1=2 ✔️
• id=6: value=0 → 标记1 → 累计求和2 →2+1=3 ✔️
• id=7: value=0 → 标记1 → 累计求和3 →3+1=4 ✔️

完全和你给出的ideal_group预期结果一致！

注意事项 #

• 必须保证ORDER BY id的逻辑正确：如果你的行顺序不是由id决定的，需要替换成实际的排序字段（比如时间戳create_time）
• 这个写法兼容绝大多数现代SQL数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等），如果是MySQL 5.x版本，可能不支持窗口函数，那只能用变量方案，但现在大部分生产环境已经升级到8.0+了

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