多表关联下获取客户特定产品最新订单及总金额的SQL问题

针对你需要的「为每个购买过特定产品的客户返回一行数据，包含最新订单日期、对应金额及指定日期范围内总金额」的需求，这里提供两种可行的SQL实现方案，解决你之前遇到的分组错误、无法关联最新订单金额的问题。

方案一：使用窗口函数（推荐，简洁高效） #

窗口函数可以在不分组的情况下计算每个客户的总金额，同时标记出最新的订单：

WITH CustomerOrders AS (
    SELECT
        c.ID AS [c.ID],
        c.Name AS [c.Name],
        o.Date AS LatestOrder,
        od.Amount AS LatestOrderAmount,
        -- 计算当前客户的所有订单金额总和
        SUM(od.Amount) OVER (PARTITION BY c.ID) AS TotalAllAmounts,
        -- 按客户分组，订单日期倒序编号，最新订单为第1行
        ROW_NUMBER() OVER (PARTITION BY c.ID ORDER BY o.Date DESC) AS RowNum
    FROM Customers c
    JOIN Orders o
        ON c.ID = o.CustomerID
    JOIN OrderDetails od
        ON o.ID = od.OrderID
        AND od.ProductID = 111
    -- 如需限定日期范围，添加以下条件
    -- WHERE o.Date BETWEEN '2021-01-01' AND '2023-12-31'
)
SELECT
    [c.ID],
    [c.Name],
    LatestOrder,
    LatestOrderAmount,
    TotalAllAmounts
FROM CustomerOrders
WHERE RowNum = 1 -- 筛选每个客户的最新订单
ORDER BY [c.ID];

逻辑说明： #

• SUM(od.Amount) OVER (PARTITION BY c.ID)：对每个客户的所有订单金额求和，无需分组即可在每行返回该客户的总金额。
• ROW_NUMBER() OVER (PARTITION BY c.ID ORDER BY o.Date DESC)：给每个客户的订单按日期从新到旧编号，最新订单的编号为1。
• 最后筛选RowNum = 1的行，即可得到每个客户的最新订单信息，同时带上总金额。

方案二：子查询分步骤计算 #

如果你的数据库不支持窗口函数（比如旧版MySQL），可以用子查询分别计算总金额和最新订单，再关联结果：

SELECT
    c.ID AS [c.ID],
    c.Name AS [c.Name],
    latest.LatestOrder,
    latest.LatestOrderAmount,
    totals.TotalAllAmounts
FROM Customers c
-- 关联客户的总订单金额
JOIN (
    SELECT
        o.CustomerID,
        SUM(od.Amount) AS TotalAllAmounts
    FROM Orders o
    JOIN OrderDetails od
        ON o.ID = od.OrderID
        AND od.ProductID = 111
    -- 如需限定日期范围，添加WHERE条件
    -- WHERE o.Date BETWEEN '2021-01-01' AND '2023-12-31'
    GROUP BY o.CustomerID
) totals ON c.ID = totals.CustomerID
-- 关联客户的最新订单信息
JOIN (
    SELECT
        o.CustomerID,
        o.Date AS LatestOrder,
        od.Amount AS LatestOrderAmount
    FROM Orders o
    JOIN OrderDetails od
        ON o.ID = od.OrderID
        AND od.ProductID = 111
    -- 匹配每个客户的最新订单日期
    WHERE (o.CustomerID, o.Date) IN (
        SELECT
            CustomerID,
            MAX(Date) AS LatestDate
        FROM Orders
        JOIN OrderDetails ON Orders.ID = OrderDetails.OrderID AND OrderDetails.ProductID = 111
        -- 如需限定日期范围，添加WHERE条件
        -- WHERE Date BETWEEN '2021-01-01' AND '2023-12-31'
        GROUP BY CustomerID
    )
) latest ON c.ID = latest.CustomerID
ORDER BY [c.ID];

逻辑说明： #

1. 第一个子查询totals：按客户分组，计算每个客户的总订单金额。
2. 第二个子查询latest：先通过子查询找到每个客户的最新订单日期，再关联回订单和订单详情表，拿到该日期对应的订单金额。
3. 最后将客户表与两个子查询关联，得到完整结果。

为什么你之前的方法出错？ #

• 直接按c.ID, c.Name, o.Date, od.Amount分组：会把每个订单拆成单独一行，因为o.Date和od.Amount是订单级别的字段，无法合并为客户级别的一行数据。
• 仅按c.ID, c.Name分组并使用MAX(o.Date)：只能得到最新日期，但无法直接关联到该日期对应的订单金额，因为MAX(o.Date)和od.Amount没有聚合关联关系，可能拿到错误的金额。

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