替换UNION语句实现更高效的SQL列转行查询

你现在的需求是把源表的Number 1和Number 2两列合并成单列Number，同时保留word1、Word 2的对应关系，原用UNION的方案因为要扫描两次表+去重合并，大数据量下性能拉胯，这里给几个只扫一次表的高效替代方案：

通用型方案（适配多数SQL数据库） #

用CROSS JOIN结合VALUES子句，只扫描一次源表就能完成列转行，再通过GROUP BY实现原UNION的去重效果：

SELECT 
    s.word1, 
    s."Word 2", 
    n.number
FROM source_table s
CROSS JOIN (
    VALUES (s."Number 1"), (s."Number 2")
) AS n(number)
WHERE n.number IS NOT NULL -- 可选，过滤空值
GROUP BY s.word1, s."Word 2", n.number

分数据库优化方案 #

PostgreSQL/BigQuery等支持数组的数据库 #

直接用UNNEST拆解数组，代码更简洁：

SELECT 
    word1, 
    "Word 2", 
    unnest(array["Number 1", "Number 2"]) AS number
FROM source_table
WHERE unnest(array["Number 1", "Number 2"]) IS NOT NULL
GROUP BY word1, "Word 2", number

MySQL 8.0+ #

利用JSON_TABLE来解析生成的JSON数组：

SELECT 
    s.word1, 
    s."Word 2", 
    j.number
FROM source_table s
JOIN JSON_TABLE(
    CONCAT('[', s."Number 1", ',', s."Number 2", ']'),
    '$[*]' COLUMNS (number INT PATH '$')
) j
WHERE j.number IS NOT NULL
GROUP BY s.word1, s."Word 2", j.number

这些方案都只需要扫描一次源表，避免了UNION两次扫描+去重的额外开销，大数据量下执行效率会显著提升，最终输出结果和你期望的完全一致。

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