如何高效将TableA非空值按顺序存入新表/视图指定列？

针对TableA中一行数据的非空值按原列顺序转移到新表/视图Col_1至Col_6（空值补在末尾）的需求，以下是各主流数据库的高效实现方案：

核心思路 #

先将原表的列转换为行结构，保留原列的顺序，过滤掉空值后重新编号，再通过条件聚合将行转回列，自动补全后续空值。这种流程逻辑清晰，性能损耗极低，单行处理时几乎瞬间完成。

PostgreSQL 实现 #

利用unnest结合WITH ORDINALITY快速将列转成带顺序的行，再通过窗口函数编号、条件聚合生成新列：

WITH ranked_non_null AS (
    SELECT 
        val,
        ROW_NUMBER() OVER () AS rn
    FROM TableA,
         unnest(ARRAY[cola, colb, colc, cold, cole, colf]) WITH ORDINALITY AS t(val, pos)
    WHERE val IS NOT NULL
    ORDER BY pos
)
SELECT
    MAX(CASE WHEN rn=1 THEN val END) AS Col_1,
    MAX(CASE WHEN rn=2 THEN val END) AS Col_2,
    MAX(CASE WHEN rn=3 THEN val END) AS Col_3,
    MAX(CASE WHEN rn=4 THEN val END) AS Col_4,
    MAX(CASE WHEN rn=5 THEN val END) AS Col_5,
    MAX(CASE WHEN rn=6 THEN val END) AS Col_6
FROM ranked_non_null;

MySQL 实现 #

通过UNION ALL逐个列转成带顺序的行，过滤空值后编号，再聚合生成新列：

WITH ranked_non_null AS (
    SELECT val, ROW_NUMBER() OVER (ORDER BY pos) AS rn
    FROM (
        SELECT cola AS val, 1 AS pos FROM TableA
        UNION ALL SELECT colb, 2 FROM TableA
        UNION ALL SELECT colc, 3 FROM TableA
        UNION ALL SELECT cold, 4 FROM TableA
        UNION ALL SELECT cole, 5 FROM TableA
        UNION ALL SELECT colf, 6 FROM TableA
    ) t
    WHERE val IS NOT NULL
)
SELECT
    MAX(CASE WHEN rn=1 THEN val END) AS Col_1,
    MAX(CASE WHEN rn=2 THEN val END) AS Col_2,
    MAX(CASE WHEN rn=3 THEN val END) AS Col_3,
    MAX(CASE WHEN rn=4 THEN val END) AS Col_4,
    MAX(CASE WHEN rn=5 THEN val END) AS Col_5,
    MAX(CASE WHEN rn=6 THEN val END) AS Col_6
FROM ranked_non_null;

SQL Server 实现 #

用CROSS APPLY + VALUES的方式将列转成行并保留顺序，后续逻辑同前：

WITH ranked_non_null AS (
    SELECT val, ROW_NUMBER() OVER (ORDER BY pos) AS rn
    FROM TableA
    CROSS APPLY (
        VALUES
            (cola, 1),
            (colb, 2),
            (colc, 3),
            (cold, 4),
            (cole, 5),
            (colf, 6)
    ) t(val, pos)
    WHERE val IS NOT NULL
)
SELECT
    MAX(CASE WHEN rn=1 THEN val END) AS Col_1,
    MAX(CASE WHEN rn=2 THEN val END) AS Col_2,
    MAX(CASE WHEN rn=3 THEN val END) AS Col_3,
    MAX(CASE WHEN rn=4 THEN val END) AS Col_4,
    MAX(CASE WHEN rn=5 THEN val END) AS Col_5,
    MAX(CASE WHEN rn=6 THEN val END) AS Col_6
FROM ranked_non_null;

性能说明 #

以上方案均采用线性处理逻辑，没有冗余的嵌套计算，单行数据处理时可以达到最优性能。如果需要批量处理多行数据，只需在CTE中添加原表的行唯一标识（比如主键），并在窗口函数和聚合时按该标识分组即可。

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