如何从多张表中按日期汇总Amount字段的总和？

问题说明 #

现有三张表OrderDetail、LearnerDetail、StoreDetail，需按OrderDate分组计算各表Amount字段的总和，最终按日期输出对应总金额。

表数据详情 #

OrderDetail

LearnerDetail

StoreDetail

原SQL问题分析 #

你提供的SQL存在两个核心问题：

• 子查询未按OrderDate过滤，计算的是全表总金额，而非对应日期的金额
• 分组仅取月份DATEPART(m, orderdate)，但期望输出完整日期，且未关联各表的日期维度

正确SQL写法 #

方法1：Union All后分组汇总 #

将三张表的日期与金额统一收集，再按日期分组求和，写法简洁高效：

SELECT 
    OrderDate,
    SUM(Amount) AS Total
FROM (
    SELECT OrderDate, Amount FROM OrderDetail
    UNION ALL
    SELECT OrderDate, Amount FROM LearnerDetail
    UNION ALL
    SELECT OrderDate, Amount FROM StoreDetail
) AS CombinedData
GROUP BY OrderDate
ORDER BY OrderDate;

方法2：按日期关联各表分组结果 #

先分别对每张表按日期求和，再通过全外连接合并日期维度，最后计算总金额，适合需要单独查看各表分日期金额的场景：

SELECT 
    COALESCE(o.OrderDate, l.OrderDate, s.OrderDate) AS OrderDate,
    COALESCE(o.Total, 0) + COALESCE(l.Total, 0) + COALESCE(s.Total, 0) AS Total
FROM (
    SELECT OrderDate, SUM(Amount) AS Total FROM OrderDetail GROUP BY OrderDate
) o
FULL OUTER JOIN (
    SELECT OrderDate, SUM(Amount) AS Total FROM LearnerDetail GROUP BY OrderDate
) l ON o.OrderDate = l.OrderDate
FULL OUTER JOIN (
    SELECT OrderDate, SUM(Amount) AS Total FROM StoreDetail GROUP BY OrderDate
) s ON COALESCE(o.OrderDate, l.OrderDate) = s.OrderDate
ORDER BY OrderDate;

最终输出结果 #

两种方法均会得到期望的结果：

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