MySQL 8.0多表查询：如何合并12个月度客户储蓄表为统一视图？

针对你12张月度客户表的汇总需求，以下是两种高效的实现方式：

方法一：多表LEFT JOIN（适合小数据量场景） #

先获取所有唯一的客户ID集合，再将每个月度表左连接到该集合，确保客户某月份无数据时仍能保留记录：

-- 生成所有唯一客户ID的临时集合
WITH all_clients AS (
    SELECT ClientID FROM January
    UNION
    SELECT ClientID FROM February
    UNION
    SELECT ClientID FROM March
    UNION
    SELECT ClientID FROM April
    UNION
    SELECT ClientID FROM May
    UNION
    SELECT ClientID FROM June
    UNION
    SELECT ClientID FROM July
    UNION
    SELECT ClientID FROM August
    UNION
    SELECT ClientID FROM September
    UNION
    SELECT ClientID FROM October
    UNION
    SELECT ClientID FROM November
    UNION
    SELECT ClientID FROM December
)
-- 左连接各月度表提取对应金额
SELECT
    ac.ClientID,
    jan.Amount AS January,
    feb.Amount AS February,
    mar.Amount AS March,
    apr.Amount AS April,
    may.Amount AS May,
    jun.Amount AS June,
    jul.Amount AS July,
    aug.Amount AS August,
    sep.Amount AS September,
    oct.Amount AS October,
    nov.Amount AS November,
    dec.Amount AS December
FROM all_clients ac
LEFT JOIN January jan ON ac.ClientID = jan.ClientID
LEFT JOIN February feb ON ac.ClientID = feb.ClientID
LEFT JOIN March mar ON ac.ClientID = mar.ClientID
LEFT JOIN April apr ON ac.ClientID = apr.ClientID
LEFT JOIN May may ON ac.ClientID = may.ClientID
LEFT JOIN June jun ON ac.ClientID = jun.ClientID
LEFT JOIN July jul ON ac.ClientID = jul.ClientID
LEFT JOIN August aug ON ac.ClientID = aug.ClientID
LEFT JOIN September sep ON ac.ClientID = sep.ClientID
LEFT JOIN October oct ON ac.ClientID = oct.ClientID
LEFT JOIN November nov ON ac.ClientID = nov.ClientID
LEFT JOIN December dec ON ac.ClientID = dec.ClientID;

方法二：UNION ALL + 条件聚合（推荐，大数据量更高效） #

先合并所有月度表数据并标记月份，再通过分组和CASE WHEN实现列转行：

SELECT
    ClientID,
    MAX(CASE WHEN month_name = 'January' THEN Amount END) AS January,
    MAX(CASE WHEN month_name = 'February' THEN Amount END) AS February,
    MAX(CASE WHEN month_name = 'March' THEN Amount END) AS March,
    MAX(CASE WHEN month_name = 'April' THEN Amount END) AS April,
    MAX(CASE WHEN month_name = 'May' THEN Amount END) AS May,
    MAX(CASE WHEN month_name = 'June' THEN Amount END) AS June,
    MAX(CASE WHEN month_name = 'July' THEN Amount END) AS July,
    MAX(CASE WHEN month_name = 'August' THEN Amount END) AS August,
    MAX(CASE WHEN month_name = 'September' THEN Amount END) AS September,
    MAX(CASE WHEN month_name = 'October' THEN Amount END) AS October,
    MAX(CASE WHEN month_name = 'November' THEN Amount END) AS November,
    MAX(CASE WHEN month_name = 'December' THEN Amount END) AS December
FROM (
    -- 合并所有月度数据并标记对应月份
    SELECT ClientID, Amount, 'January' AS month_name FROM January
    UNION ALL
    SELECT ClientID, Amount, 'February' AS month_name FROM February
    UNION ALL
    SELECT ClientID, Amount, 'March' AS month_name FROM March
    UNION ALL
    SELECT ClientID, Amount, 'April' AS month_name FROM April
    UNION ALL
    SELECT ClientID, Amount, 'May' AS month_name FROM May
    UNION ALL
    SELECT ClientID, Amount, 'June' AS month_name FROM June
    UNION ALL
    SELECT ClientID, Amount, 'July' AS month_name FROM July
    UNION ALL
    SELECT ClientID, Amount, 'August' AS month_name FROM August
    UNION ALL
    SELECT ClientID, Amount, 'September' AS month_name FROM September
    UNION ALL
    SELECT ClientID, Amount, 'October' AS month_name FROM October
    UNION ALL
    SELECT ClientID, Amount, 'November' AS month_name FROM November
    UNION ALL
    SELECT ClientID, Amount, 'December' AS month_name FROM December
) AS all_monthly_data
GROUP BY ClientID;

注意事项 #

• 若你的表中客户ID字段实际拼写为ClienID（如示例所示），请将上述SQL中的ClientID替换为ClienID；
• 若需要将无数据月份的NULL显示为0，可将MAX(CASE ...)修改为COALESCE(MAX(CASE ...), 0)。

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