如何为SQL表添加层级列？基于百位规则构建层级结构

问题描述 #

我的SQL表结构如下：

需要为表添加level列来构建层级结构：

• unit_number末两位为00的记录（如1700、1800）设为层级1
• 同前缀的后续记录（如18开头的1801、1802等）依次设为层级2、层级3...
• 非数字格式的unit_number（如F&B、PRODUCT.）默认设为层级1

解决方案 #

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

利用窗口函数按前缀分组排序，快速计算层级：

SELECT 
    unit_number,
    name,
    party_number,
    CASE
        -- 非数字格式的unit_number默认层级1
        WHEN NOT unit_number REGEXP '^[0-9]+$' THEN 1
        -- 末两位为00的标记为层级1
        WHEN RIGHT(unit_number, 2) = '00' THEN 1
        -- 同前缀下的其他记录，按顺序递增层级
        ELSE 1 + ROW_NUMBER() OVER (
            PARTITION BY LEFT(unit_number, LENGTH(unit_number)-2) 
            ORDER BY unit_number
        )
    END AS level
FROM your_table_name
ORDER BY unit_number;

方案2：MySQL 5.x（不支持窗口函数） #

用子查询统计同前缀下的前置记录数来计算层级：

SELECT 
    t1.unit_number,
    t1.name,
    t1.party_number,
    CASE
        WHEN NOT t1.unit_number REGEXP '^[0-9]+$' THEN 1
        WHEN RIGHT(t1.unit_number, 2) = '00' THEN 1
        ELSE 1 + (
            SELECT COUNT(*) 
            FROM your_table_name t2
            WHERE LEFT(t2.unit_number, LENGTH(t2.unit_number)-2) = LEFT(t1.unit_number, LENGTH(t1.unit_number)-2)
              AND t2.unit_number < t1.unit_number
              AND RIGHT(t2.unit_number, 2) != '00'
        )
    END AS level
FROM your_table_name t1
ORDER BY t1.unit_number;

注意事项 #

• 请将your_table_name替换为实际的表名
• 如果需要调整非数字unit_number的层级规则，修改CASE分支中的对应逻辑即可

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