如何在SQL中按分组识别连续数字并生成组ID？

刚好之前解决过类似的分组内连续数字分组问题，给你一个通用的窗口函数方案，完美适配你的场景：

完整SQL代码 #

-- 注意：不同数据库对关键字"group"的转义方式不同
-- MySQL用`group`，SQL Server用[group]，PostgreSQL用"group"
WITH ranked_data AS (
    SELECT 
        id,
        "group",
        -- 按分组对id排序，生成组内行号
        ROW_NUMBER() OVER (PARTITION BY "group" ORDER BY id) AS rn
    FROM Table1
),
continuous_groups AS (
    SELECT 
        id,
        "group",
        -- 连续id与行号的差值固定，作为分组标识
        id - rn AS group_key
    FROM ranked_data
)
SELECT 
    id,
    "group",
    -- 对每个分组内的group_key分配连续组ID
    DENSE_RANK() OVER (PARTITION BY "group" ORDER BY group_key) AS result
FROM continuous_groups
ORDER BY "group", id;

思路拆解 #

我来一步步给你讲清楚逻辑：

1. 生成分组内行号
   在ranked_data这个临时结果集里，用ROW_NUMBER()窗口函数按"group"分组、id升序排列，给每个id分配组内的顺序编号。比如group=a里的id1到5，行号就是1到5；id8的行号是6，以此类推。

2. 识别连续数字的分组标识
   连续的数字和对应的行号做减法，得到的差值是固定的！比如group=a里的id1：1-1=0，id2：2-2=0……id5：5-5=0，这几个差值相同，说明是连续的序列；而id8的行号是6，8-6=2，和前面的差值不一样，就是新的独立分组。

3. 生成最终组ID
   最后用DENSE_RANK()窗口函数，按"group"分组、group_key排序，给每个不同的group_key分配连续的序号，这就是你要的result列。

验证示例数据 #

用你给出的Table1数据测试，结果完全匹配你示例中的result列：

• group=a里，id1-5的group_key都是0，对应result=1；id8的group_key=2，对应result=2；id13-16的group_key=5，对应result=4。
• group=b的情况同理，所有连续id都会被正确归为同一组，组ID连续递增。

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