如何对三张表执行相同SQL聚合查询并合并结果？

要实现将三张表（A、B、C）的按日期聚合结果合并为指定的宽表格式，有两种常用的SQL方案，以下是具体实现：

方案一：使用CTE + FULL JOIN（通用兼容所有SQL数据库） #

这种方法通过先分别计算每张表的聚合结果，再通过全连接（FULL JOIN）合并所有日期，确保不会遗漏任何表中存在的日期：

WITH agg_a AS (
    SELECT date, SUM(number) AS a_number
    FROM A
    GROUP BY date
),
agg_b AS (
    SELECT date, SUM(number) AS b_number
    FROM B
    GROUP BY date
),
agg_c AS (
    SELECT date, SUM(number) AS c_number
    FROM C
    GROUP BY date
)
SELECT 
    COALESCE(a.date, b.date, c.date) AS date,
    a.a_number AS "A.number",
    b.b_number AS "B.number",
    c.c_number AS "C.number"
FROM agg_a a
FULL JOIN agg_b b ON a.date = b.date
FULL JOIN agg_c c ON COALESCE(a.date, b.date) = c.date
ORDER BY date;

关键说明： #

• CTE（公共表表达式）将每张表的聚合逻辑拆分，让代码更易读和维护
• FULL JOIN 保证所有表中的日期都会被包含，即使某张表没有该日期的数据
• COALESCE 用来统一日期列的值，避免出现NULL
• 最终结果按日期排序，保证输出有序

方案二：使用UNION ALL + PIVOT（简洁但依赖数据库支持） #

如果你的数据库支持PIVOT语法（如SQL Server、Oracle），可以先将所有聚合结果合并，再通过透视转换为宽表：

WITH all_agg AS (
    SELECT 'A' AS table_name, date, SUM(number) AS total
    FROM A
    GROUP BY date
    UNION ALL
    SELECT 'B' AS table_name, date, SUM(number) AS total
    FROM B
    GROUP BY date
    UNION ALL
    SELECT 'C' AS table_name, date, SUM(number) AS total
    FROM C
    GROUP BY date
)
SELECT 
    date,
    [A] AS "A.number",
    [B] AS "B.number",
    [C] AS "C.number"
FROM all_agg
PIVOT (
    SUM(total)
    FOR table_name IN ([A], [B], [C])
) AS pivot_table
ORDER BY date;

关键说明： #

• UNION ALL 将三张表的聚合结果合并为一个包含表标识的数据集
• PIVOT 将表标识列（table_name）转换为列名，同时聚合对应的值
• 若使用MySQL等不支持原生PIVOT的数据库，可以用CASE WHEN模拟透视逻辑：

WITH all_agg AS (
    SELECT 'A' AS table_name, date, SUM(number) AS total
    FROM A
    GROUP BY date
    UNION ALL
    SELECT 'B' AS table_name, date, SUM(number) AS total
    FROM B
    GROUP BY date
    UNION ALL
    SELECT 'C' AS table_name, date, SUM(number) AS total
    FROM C
    GROUP BY date
)
SELECT 
    date,
    SUM(CASE WHEN table_name = 'A' THEN total END) AS "A.number",
    SUM(CASE WHEN table_name = 'B' THEN total END) AS "B.number",
    SUM(CASE WHEN table_name = 'C' THEN total END) AS "C.number"
FROM all_agg
GROUP BY date
ORDER BY date;

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