原表无分组标识时，如何在SQL Server中实现分组及成员编号？

原始数据表：

期望结果：

规则：每当Rol字段值为Leader时创建新分组，每个分组内生成连续的MemberId。

可以通过累计求和生成GroupId，再结合ROW_NUMBER()函数生成分组内的MemberId，SQL代码如下：

SELECT 
    Name,
    Rol,
    -- 累计统计Leader出现次数，生成连续分组ID
    SUM(CASE WHEN Rol = 'Leader' THEN 1 ELSE 0 END) OVER (ORDER BY FirstDate) AS GroupId,
    -- 按分组ID分区，生成组内连续编号
    ROW_NUMBER() OVER (PARTITION BY SUM(CASE WHEN Rol = 'Leader' THEN 1 ELSE 0 END) OVER (ORDER BY FirstDate) ORDER BY FirstDate) AS MemberId
FROM 
    your_table_name
ORDER BY 
    FirstDate;

代码解释 #

1. GroupId生成：利用SUM() OVER (ORDER BY FirstDate)对所有行按日期排序，每遇到Rol='Leader'的行就累计加1，自动生成连续的分组ID。
2. MemberId生成：以生成的GroupId作为分区条件，用ROW_NUMBER()函数在每个分组内按日期排序，生成从1开始的连续编号。

如果数据库支持CTE（公共表达式），可以将分组ID的计算逻辑抽离，让代码更易读：

WITH grouped_data AS (
    SELECT 
        Name,
        Rol,
        FirstDate,
        SUM(CASE WHEN Rol = 'Leader' THEN 1 ELSE 0 END) OVER (ORDER BY FirstDate) AS GroupId
    FROM your_table_name
)
SELECT 
    Name,
    Rol,
    GroupId,
    ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY FirstDate) AS MemberId
FROM grouped_data
ORDER BY FirstDate;

内容的提问来源于stack exchange，提问作者Diego R.