按ID分配行号：指定值固定行号的SQL查询实现

需求分析 #

按myid分组分配行号：

• col2为A时，行号固定为1
• col2为B时，行号固定为2
• col2为其他值时，从3开始按连续顺序分配

通用SQL实现（兼容MySQL 8+、PostgreSQL、SQL Server等） #

WITH ranked AS (
    SELECT
        myid,
        col1,
        col2,
        -- 按A、B、其他的顺序给分组内的行排序，col1可替换为实际需要的排序字段
        ROW_NUMBER() OVER (
            PARTITION BY myid
            ORDER BY (CASE col2 WHEN 'A' THEN 1 WHEN 'B' THEN 2 ELSE 3 END), col1
        ) AS rn,
        -- 统计每个分组内A和B的总行数
        COUNT(CASE WHEN col2 IN ('A','B') THEN 1 END) OVER (PARTITION BY myid) AS ab_count
    FROM your_table
)
SELECT
    myid,
    col1,
    col2,
    CASE
        WHEN col2 = 'A' THEN 1
        WHEN col2 = 'B' THEN 2
        -- 非A/B的行，用排序序号减去A/B的总数，再加上2得到从3开始的连续编号
        ELSE 2 + (rn - ab_count)
    END AS row_number
FROM ranked
ORDER BY myid, row_number;

逻辑说明 #

1. CTEranked完成两个核心操作：
   • 对每个myid分组内的行排序，确保A、B排在最前面，其他值按指定字段（示例用col1）排序
   • 计算每个分组内A和B的总行数ab_count
2. 主查询通过CASE分支分配行号：
   • 匹配A/B直接返回固定值
   • 其他行通过rn - ab_count得到其在非A/B行中的相对位置，再加2得到从3开始的连续编号

验证结果 #

执行后将得到与期望一致的结果：

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