如何使用带分区的row_number()无需额外排序实现数值变化时序号重置

实现思路 #

你需要的是对连续出现的相同Num值做组内编号，属于SQL典型的「数据孤岛」场景，核心逻辑是先给连续相同的Num划分到同一个分组，再对每个分组内的行按Id升序编号即可。

通用窗口函数解法（支持MySQL 8.0+、PostgreSQL、SQL Server、Oracle等主流数据库） #

WITH grp_cte AS (
    SELECT 
        Id,
        Num,
        -- 计算分组标记：每当Num和上一行不同时分组标记+1
        SUM(CASE WHEN Num = LAG(Num) OVER(ORDER BY Id) THEN 0 ELSE 1 END) OVER(ORDER BY Id) AS grp_id
    FROM table_1
)
SELECT 
    Id,
    Num,
    -- 每个分组内按Id升序编号，就是目标row_num
    ROW_NUMBER() OVER(PARTITION BY grp_id ORDER BY Id) AS row_num
FROM grp_cte
ORDER BY Id;

MySQL 5.x 无窗口函数兼容解法 #

如果你的数据库版本不支持窗口函数，可以用自定义变量实现：

SELECT 
    Id,
    Num,
    row_num
FROM (
    SELECT 
        t.Id,
        t.Num,
        @row_num := IF(@prev_num = t.Num, @row_num + 1, 1) AS row_num,
        @prev_num := t.Num
    FROM table_1 t, (SELECT @prev_num := NULL, @row_num := 0) AS init
    ORDER BY t.Id
) AS res;

以上两种方案的执行结果完全匹配你给出的预期输出。

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