咨询：使用UNION ALL合并列名不同的相似表并补全缺失列

嘿，这个需求在日常数据处理里挺常见的，我来一步步给你讲清楚怎么实现～

首先核心逻辑很明确：UNION（或者UNION ALL）要求参与合并的两张表必须有相同的列数、列顺序，且对应列的数据类型一致。所以我们要做的就是把两张表都补成完全一致的列结构，对于各自没有的字段，用NULL或者0来填充，之后就能顺利合并，再做GROUP BY也没问题。

一、用NULL填充非重叠字段 #

假设你有两张表：

• table_a：包含字段 id, username, order_amount, create_at
• table_b：包含字段 id, username, refund_amount, update_at

其中order_amount和refund_amount、create_at和update_at是互相不重叠的字段，那填充NULL的SQL写法如下：

-- 先查table_a，补全table_b有的但自己没有的字段，用NULL填充
SELECT
    id,
    username,
    order_amount,
    NULL AS refund_amount,  -- 补上table_b的refund_amount字段
    create_at,
    NULL AS update_at       -- 补上table_b的update_at字段
FROM table_a
UNION ALL  -- 用UNION ALL比UNION高效，不需要去重就用它
-- 再查table_b，补全table_a有的但自己没有的字段，用NULL填充
SELECT
    id,
    username,
    NULL AS order_amount,  -- 补上table_a的order_amount字段
    refund_amount,
    NULL AS create_at,     -- 补上table_a的create_at字段
    update_at
FROM table_b;

二、用0填充数字类型的非重叠字段 #

如果非重叠字段是数字类型（比如上面的order_amount和refund_amount），你想把缺失值填成0而不是NULL，只需要把对应的NULL换成0就行：

SELECT
    id,
    username,
    order_amount,
    0 AS refund_amount,  -- 数字字段填充0
    create_at,
    NULL AS update_at    -- 日期类型只能用NULL（或者空字符串，但NULL更规范）
FROM table_a
UNION ALL
SELECT
    id,
    username,
    0 AS order_amount,  -- 数字字段填充0
    refund_amount,
    NULL AS create_at,
    update_at
FROM table_b;

⚠️ 注意：非数字类型的字段（比如日期、字符串）千万别填0，会因为数据类型不匹配报错，这类字段只能用NULL或者对应类型的默认值（比如空字符串''）。

三、合并后执行GROUP BY操作 #

合并完成后，你可以直接在结果上做GROUP BY，用CTE（公共表表达式）来写会更清晰：

WITH combined_table AS (
    SELECT
        id,
        username,
        order_amount,
        0 AS refund_amount,
        create_at,
        NULL AS update_at
    FROM table_a
    UNION ALL
    SELECT
        id,
        username,
        0 AS order_amount,
        refund_amount,
        NULL AS create_at,
        update_at
    FROM table_b
)
-- 按id和username分组，统计总收支
SELECT
    id,
    username,
    SUM(order_amount) AS total_order,
    SUM(refund_amount) AS total_refund,
    SUM(order_amount - refund_amount) AS net_amount
FROM combined_table
GROUP BY id, username;

几个关键注意点 #

• 确保对应列的数据类型一致：比如table_a的order_amount是DECIMAL(10,2)，table_b的refund_amount最好也是同类型，避免隐式转换带来的问题。
• 优先用UNION ALL：如果不需要去重，UNION ALL的执行效率比UNION高很多，因为UNION会额外做去重操作。
• 列顺序要严格对应：UNION是按列的位置来匹配的，不是按字段名，所以两张表的列顺序必须完全一致。

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