如何反转表中的层级列并处理未知数量的Null值？

核心思路 #

先将每行的非Null层级值按原有顺序聚合为有序集合，反转该集合后，再将元素拆分到对应的反转列中，自动忽略原有的Null值，同时保留反转后的空位置。

具体实现（按主流数据库分类） #

假设原表为hierarchy_table，层级列是level1、level2、level3、level4，目标生成反转列reverse1、reverse2、reverse3、reverse4。

PostgreSQL 版本

WITH reversed_hierarchy AS (
    SELECT
        id, -- 假设表有主键id用于关联
        ARRAY_REVERSE(ARRAY_REMOVE(ARRAY[level1, level2, level3, level4], NULL)) AS reversed_array
    FROM hierarchy_table
)
UPDATE hierarchy_table ht
SET
    reverse1 = rh.reversed_array[1],
    reverse2 = rh.reversed_array[2],
    reverse3 = rh.reversed_array[3],
    reverse4 = rh.reversed_array[4]
FROM reversed_hierarchy rh
WHERE ht.id = rh.id;

MySQL 版本

利用JSON函数处理非Null值的聚合与反转：

UPDATE hierarchy_table ht
JOIN (
    SELECT
        id,
        JSON_EXTRACT(JSON_REVERSE(JSON_ARRAYAGG(level_val)), '$[0]') AS reverse1,
        JSON_EXTRACT(JSON_REVERSE(JSON_ARRAYAGG(level_val)), '$[1]') AS reverse2,
        JSON_EXTRACT(JSON_REVERSE(JSON_ARRAYAGG(level_val)), '$[2]') AS reverse3,
        JSON_EXTRACT(JSON_REVERSE(JSON_ARRAYAGG(level_val)), '$[3]') AS reverse4
    FROM (
        SELECT id, level1 AS level_val FROM hierarchy_table WHERE level1 IS NOT NULL
        UNION ALL
        SELECT id, level2 AS level_val FROM hierarchy_table WHERE level2 IS NOT NULL
        UNION ALL
        SELECT id, level3 AS level_val FROM hierarchy_table WHERE level3 IS NOT NULL
        UNION ALL
        SELECT id, level4 AS level_val FROM hierarchy_table WHERE level4 IS NOT NULL
    ) AS non_null_levels
    GROUP BY id
) AS rh ON ht.id = rh.id
SET
    ht.reverse1 = rh.reverse1,
    ht.reverse2 = rh.reverse2,
    ht.reverse3 = rh.reverse3,
    ht.reverse4 = rh.reverse4;

SQL Server 版本

通过字符串聚合与反转实现：

WITH reversed_hierarchy AS (
    SELECT
        id,
        STRING_AGG(level_val, ',') WITHIN GROUP (ORDER BY pos DESC) AS reversed_str
    FROM (
        SELECT id, level1 AS level_val, 1 AS pos FROM hierarchy_table WHERE level1 IS NOT NULL
        UNION ALL
        SELECT id, level2 AS level_val, 2 AS pos FROM hierarchy_table WHERE level2 IS NOT NULL
        UNION ALL
        SELECT id, level3 AS level_val, 3 AS pos FROM hierarchy_table WHERE level3 IS NOT NULL
        UNION ALL
        SELECT id, level4 AS level_val, 4 AS pos FROM hierarchy_table WHERE level4 IS NOT NULL
    ) AS non_null_levels
    GROUP BY id
)
UPDATE ht
SET
    reverse1 = PARSENAME(REPLACE(rh.reversed_str, ',', '.'), 4),
    reverse2 = PARSENAME(REPLACE(rh.reversed_str, ',', '.'), 3),
    reverse3 = PARSENAME(REPLACE(rh.reversed_str, ',', '.'), 2),
    reverse4 = PARSENAME(REPLACE(rh.reversed_str, ',', '.'), 1)
FROM hierarchy_table ht
JOIN reversed_hierarchy rh ON ht.id = rh.id;

方案优势 #

• 仅需单次更新操作，替代多条逐列UPDATE，执行效率更高
• 自动适配任意数量的Null值，无需手动判断每个列的非空状态
• 语法适配主流关系型数据库，可根据实际环境调整

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