如何在SQL中按指定起止值划分数据区间生成分组rank

你这个需求属于典型的按顺序识别区间起始点打分组标签的场景，核心逻辑是：按数据原有顺序遍历，每当遇到to_part=1且前一个值不是1（即前一个值是-1，或是第一行）时，就开启一个新分组，同区间内所有行共用同一个分组编号。

注意：SQL表本身无固有顺序，实现前必须先确定能标识数据原始顺序的字段（比如自增主键、创建时间、业务流水号等），否则分组结果不可控。

支持窗口函数的数据库写法（MySQL 8.0+/PostgreSQL/SQL Server/Oracle等） #

用窗口函数LAG()获取上一行的to_part值，给新分组起始行打标记后累计求和即可得到分组编号：

WITH ordered_data AS (
    SELECT 
        id,
        to_part,
        -- 实际使用时把ORDER BY后面的内容替换为你的顺序字段，比如ORDER BY id, create_time
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rn
    FROM your_table
),
flagged_data AS (
    SELECT
        id,
        to_part,
        rn,
        -- 新分组起始行标记为1，其余为0
        CASE 
            WHEN to_part = 1 AND LAG(to_part, 1, -1) OVER (ORDER BY rn) != 1 
            THEN 1 
            ELSE 0 
        END AS new_group_flag
    FROM ordered_data
)
SELECT
    id,
    to_part,
    -- 累计求和得到连续分组编号
    SUM(new_group_flag) OVER (ORDER BY rn) AS `rank`
FROM flagged_data
ORDER BY rn;

执行后返回的结果和你给出的预期输出完全一致。

老版本MySQL（5.x）无窗口函数的写法 #

用用户变量遍历逐行计算分组编号即可：

SELECT
    id,
    to_part,
    @rank := CASE
        WHEN to_part = 1 AND @last_to_part != 1 THEN @rank + 1
        ELSE @rank
    END AS `rank`,
    @last_to_part := to_part
FROM your_table,
(SELECT @rank := 0, @last_to_part := -1) AS init
-- 必须替换为你的实际排序字段，否则结果无意义
ORDER BY 自增ID/创建时间字段;

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