如何在SQL中基于另一列的值创建分组索引？

需求说明 #

需要将表中数据按Gap列的Start到End区间分组（包含区间内连续的Start行），为每组分配递增的索引值。

原表数据 #

SeqNo   Gap
20      Start
21      End
29      Start
30      End
42      Start
43      End
49      Start
50      Start
51      Start
52      Start
53      Start
54      Start
55      End
220     Start   
221     Start   
222     End

期望输出 #

SeqNo   Gap    Index
20      Start     1
21      End       1
29      Start     2
30      End       2
42      Start     3
43      End       3
49      Start     4
50      Start     4
51      Start     4
52      Start     4
53      Start     4
54      Start     4
55      End       4
220     Start     5
221     Start     5
222     End       5

解决方案 #

方法1：支持窗口函数的数据库（MySQL 8+、PostgreSQL、SQL Server等）

利用窗口累计求和，统计每行之前Start出现的次数作为分组索引：

SELECT 
    SeqNo,
    Gap,
    SUM(CASE WHEN Gap = 'Start' THEN 1 ELSE 0 END) OVER (ORDER BY SeqNo) AS Index
FROM your_table_name
ORDER BY SeqNo;

逻辑说明：

• 通过CASE语句将Start标记为1，其他行标记为0
• SUM() OVER (ORDER BY SeqNo)按SeqNo顺序累计标记值，每遇到一个Start，累计值递增，后续行（包括连续Start和对应End）沿用该值，形成分组索引

方法2：不支持窗口函数的旧版MySQL

使用用户变量实现累计计数：

SELECT 
    SeqNo,
    Gap,
    @index := @index + (Gap = 'Start') AS Index
FROM your_table_name, (SELECT @index := 0) AS init
ORDER BY SeqNo;

逻辑说明：

• 初始化变量@index为0
• 每行判断Gap是否为Start，是则变量加1，否则保持原值，最终得到分组索引

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