如何合并两个带计算列的SQL语句？解决UNION ALL结果空值问题

原写法的逻辑缺陷 #

你目前使用UNION ALL上下拼接两个独立聚合结果的设计，从根源上会导致同一家公司被拆为两行、其中一个分数列固定为NULL的问题——哪怕外层做空值过滤，也没法实现「单公司一行、同时展示两个分数」的预期效果，属于治标不治本。

最优实现方案（从根源消除空值） #

完全不需要用UNION ALL做上下拼接，只需要先分别对两个表按公司维度算好对应分数，再以公司为关联键做全外连接，把两个表的结果对齐到同一行，最后用COALESCE把没有对应数值的位置补为0，就能直接得到无空值、无重复行的单表结果：

WITH table1_agg AS (
    -- 单独聚合Table1的score1，不手动插入NULL列
    SELECT 
        Company,
        SUM(CASE
            WHEN voltage = '99 kV' THEN number * 1
            WHEN voltage = '199 kV' THEN number * 2
            WHEN voltage = '299 kV' THEN number * 3
            WHEN voltage = '399 kV' THEN number * 4
            WHEN voltage = '599 kV' THEN number * 5
            WHEN voltage = '799 kV' THEN number * 6
            ELSE 0
        END) AS score1
    FROM Table1    
    WHERE [year] = '2020'     
    GROUP BY Company
),
table2_agg AS (
    -- 单独聚合Table2的score2
    SELECT 
        Company,
        SUM(CASE
            WHEN voltage = '99 kV' THEN number * 1
            WHEN voltage = '199 kV' THEN number * 2
            WHEN voltage = '299 kV' THEN number * 3
            WHEN voltage = '399 kV' THEN number * 4
            WHEN voltage = '599 kV' THEN number * 5
            WHEN voltage = '799 kV' THEN number * 6
            ELSE 0
        END) AS score2
    FROM Table2    
    WHERE [year] = '2020'     
    GROUP BY Company
)
SELECT
    -- 兼容两个表所有出现过的公司，不会漏数据
    COALESCE(t1.Company, t2.Company) AS Company,
    -- 没有对应分数的位置直接补0，替代原有NULL
    COALESCE(t1.score1, 0) AS score1,
    COALESCE(t2.score2, 0) AS score2
FROM table1_agg t1
FULL OUTER JOIN table2_agg t2
    ON t1.Company = t2.Company
ORDER BY Company

适配原有UNION ALL框架的快速修改方案 #

如果你不想调整原有查询的整体结构，只需要两个小改动就能实现无空值的单表结果：

• 把UNION两段查询里手动写的NULL AS 对应分数列替换成0 AS 对应分数列，从根源避免NULL值出现
• 在外层套一层按Company分组的聚合，把同一家公司的两行分数合并到一行
  修改后的代码如下：

SELECT
    Company,
    SUM(score1) AS score1,
    SUM(score2) AS score2
FROM (
    SELECT 
        Company,
        SUM(CASE
            WHEN voltage = '99 kV' THEN number * 1
            WHEN voltage = '199 kV' THEN number * 2
            WHEN voltage = '299 kV' THEN number * 3
            WHEN voltage = '399 kV' THEN number * 4
            WHEN voltage = '599 kV' THEN number * 5
            WHEN voltage = '799 kV' THEN number * 6
            ELSE 0
        END) AS score1,
        -- 替换原写法的NULL为0
        0 AS score2
    FROM Table1    
    WHERE [year] = '2020'     
    GROUP BY Company    

    UNION ALL    

    SELECT 
        Company,
        -- 替换原写法的NULL为0
        0 AS score1,
        SUM(CASE
            WHEN voltage = '99 kV' THEN number * 1
            WHEN voltage = '199 kV' THEN number * 2
            WHEN voltage = '299 kV' THEN number * 3
            WHEN voltage = '399 kV' THEN number * 4
            WHEN voltage = '599 kV' THEN number * 5
            WHEN voltage = '799 kV' THEN number * 6
            ELSE 0
        END) AS score2
    FROM Table2    
    WHERE [year] = '2020'     
    GROUP BY Company
) raw_result
GROUP BY Company
ORDER BY Company

方案对比说明 #

• 两种写法最终都能实现：同一家公司只占一行、两个分数列无空值、两个表出现过的所有公司都不会遗漏
• 带全外连接的CTE方案性能更优，因为聚合后的临时表数据量远小于全表UNION的结果量，关联计算的开销更低

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