SQL需求：多表按matricula与月份聚合求和，支持部分表无数据

核心思路 #

要实现跨多表按matricula和月份分组求和，同时保证只要任意表有对应数据就返回结果，优先采用**UNION ALL结合聚合**的方式（兼容性更强），也可针对支持FULL OUTER JOIN的数据库使用关联查询方案。

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

WITH monthly_costs AS (
    -- 处理table1，提取月份并聚合成本
    SELECT 
        matricula,
        DATE_TRUNC('month', date) AS month_date,
        SUM(cost_One) AS cost_one_sum,
        0 AS cost_two_sum,
        0 AS cost_three_sum
    FROM table1
    GROUP BY matricula, DATE_TRUNC('month', date)
    
    UNION ALL
    
    -- 处理table2
    SELECT 
        matricula,
        DATE_TRUNC('month', date) AS month_date,
        0 AS cost_one_sum,
        SUM(cost_Two) AS cost_two_sum,
        0 AS cost_three_sum
    FROM table2
    GROUP BY matricula, DATE_TRUNC('month', date)
    
    UNION ALL
    
    -- 处理table3
    SELECT 
        matricula,
        DATE_TRUNC('month', date) AS month_date,
        0 AS cost_one_sum,
        0 AS cost_two_sum,
        SUM(cost_Three) AS cost_three_sum
    FROM table3
    GROUP BY matricula, DATE_TRUNC('month', date)
)
SELECT 
    matricula,
    month_date,
    SUM(cost_one_sum) AS total_cost_one,
    SUM(cost_two_sum) AS total_cost_two,
    SUM(cost_three_sum) AS total_cost_three,
    SUM(cost_one_sum + cost_two_sum + cost_three_sum) AS total_all_costs
FROM monthly_costs
GROUP BY matricula, month_date
ORDER BY matricula, month_date;

语法适配说明 #

不同数据库的日期截断语法有差异，替换DATE_TRUNC('month', date)即可：

• MySQL：DATE_FORMAT(date, '%Y-%m-01')
• Oracle：TRUNC(date, 'MM')
• SQL Server：DATEADD(month, DATEDIFF(month, 0, date), 0)

支持FULL OUTER JOIN的数据库方案（如PostgreSQL、SQL Server） #

SELECT 
    COALESCE(t1.matricula, t2.matricula, t3.matricula) AS matricula,
    COALESCE(t1.month_date, t2.month_date, t3.month_date) AS month_date,
    COALESCE(t1.cost_one_sum, 0) AS total_cost_one,
    COALESCE(t2.cost_two_sum, 0) AS total_cost_two,
    COALESCE(t3.cost_three_sum, 0) AS total_cost_three,
    COALESCE(t1.cost_one_sum, 0) + COALESCE(t2.cost_two_sum, 0) + COALESCE(t3.cost_three_sum, 0) AS total_all_costs
FROM (
    SELECT matricula, DATE_TRUNC('month', date) AS month_date, SUM(cost_One) AS cost_one_sum
    FROM table1
    GROUP BY matricula, DATE_TRUNC('month', date)
) t1
FULL OUTER JOIN (
    SELECT matricula, DATE_TRUNC('month', date) AS month_date, SUM(cost_Two) AS cost_two_sum
    FROM table2
    GROUP BY matricula, DATE_TRUNC('month', date)
) t2 ON t1.matricula = t2.matricula AND t1.month_date = t2.month_date
FULL OUTER JOIN (
    SELECT matricula, DATE_TRUNC('month', date) AS month_date, SUM(cost_Three) AS cost_three_sum
    FROM table3
    GROUP BY matricula, DATE_TRUNC('month', date)
) t3 ON COALESCE(t1.matricula, t2.matricula) = t3.matricula AND COALESCE(t1.month_date, t2.month_date) = t3.month_date
ORDER BY matricula, month_date;

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