SQL相似表合并：年月维度关联及缺失字段补0实现

要实现你需要的合并效果，核心思路是保留两个表中所有的Year-Month组合，然后对缺失的字段值用0填充。下面分不同SQL环境给出具体实现：

一、标准SQL（支持FULL OUTER JOIN，如PostgreSQL、SQL Server等） #

使用FULL OUTER JOIN关联两个表的Year和Month字段，再用COALESCE()函数将NULL值替换为0：

SELECT
    COALESCE(t1.Year, t2.Year) AS Year,
    COALESCE(t1.Month, t2.Month) AS Month,
    COALESCE(t2.TaRR, 0) AS TaRR,
    COALESCE(t1.EMS, 0) AS EMS
FROM table1 t1
FULL OUTER JOIN table2 t2
    ON t1.Year = t2.Year 
    AND t1.Month = t2.Month
ORDER BY Year, 
    -- 按月份顺序排序，可根据实际月份类型调整
    CASE Month
        WHEN 'January' THEN 1
        WHEN 'February' THEN 2
        WHEN 'March' THEN 3
        WHEN 'April' THEN 4
        WHEN 'October' THEN 10
        ELSE 11
    END;

关键解释： #

• FULL OUTER JOIN会保留两个表中所有匹配和不匹配的Year-Month组合，不管是只在table1还是只在table2出现的记录都会被保留。
• COALESCE(a, b)函数会返回第一个非NULL的值，所以当某个表没有对应记录时，就会用0替代NULL。
• 最后的ORDER BY是为了让结果按年份和月份逻辑顺序排列，如果你的Month字段是数字类型（比如1代表一月），直接用Month排序即可，不需要CASE语句。

二、MySQL（不支持FULL OUTER JOIN） #

MySQL没有原生的FULL OUTER JOIN，我们可以用UNION ALL先收集所有唯一的Year-Month组合，再分别左连接两个表：

-- 先获取所有唯一的Year-Month对（支持MySQL 8.0+的CTE语法）
WITH all_dates AS (
    SELECT Year, Month FROM table1
    UNION
    SELECT Year, Month FROM table2
)
SELECT
    ad.Year,
    ad.Month,
    COALESCE(t2.TaRR, 0) AS TaRR,
    COALESCE(t1.EMS, 0) AS EMS
FROM all_dates ad
LEFT JOIN table1 t1
    ON ad.Year = t1.Year AND ad.Month = t1.Month
LEFT JOIN table2 t2
    ON ad.Year = t2.Year AND ad.Month = t2.Month
ORDER BY ad.Year, 
    CASE ad.Month
        WHEN 'January' THEN 1
        WHEN 'February' THEN 2
        WHEN 'March' THEN 3
        WHEN 'April' THEN 4
        WHEN 'October' THEN 10
        ELSE 11
    END;

如果你的MySQL版本不支持CTE（WITH语句），可以换成子查询写法：

SELECT
    ad.Year,
    ad.Month,
    COALESCE(t2.TaRR, 0) AS TaRR,
    COALESCE(t1.EMS, 0) AS EMS
FROM (
    SELECT Year, Month FROM table1
    UNION
    SELECT Year, Month FROM table2
) ad
LEFT JOIN table1 t1
    ON ad.Year = t1.Year AND ad.Month = t1.Month
LEFT JOIN table2 t2
    ON ad.Year = t2.Year AND ad.Month = t2.Month
ORDER BY ad.Year, 
    CASE ad.Month
        WHEN 'January' THEN 1
        WHEN 'February' THEN 2
        WHEN 'March' THEN 3
        WHEN 'April' THEN 4
        WHEN 'October' THEN 10
        ELSE 11
    END;

验证结果 #

用你给出的示例数据测试，两种方法都会生成你期望的结果：

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