如何使用SUM函数关联三张以客户名为主键的数据表？

首先修正原语句里的小问题：Dispatch子查询中SELECT的字段是Storer，但GROUP BY是Customer，这里应该统一为Customer。

要关联三张表并分别求和，建议先对每张表单独按Customer聚合计算总和，再通过LEFT JOIN关联，避免多表直接关联时产生笛卡尔积导致求和结果错误。假设Receipt表中需要求和的数量字段为[Received Quantity]，如果实际字段名不同，你可以自行替换：

SELECT
    COALESCE(s.Customer, d.Customer, r.Customer) AS Customer,
    ISNULL(s.AvailableQty, 0) AS AvailableQty,
    ISNULL(d.ShippedQty, 0) AS ShippedQty,
    ISNULL(r.ReceivedQty, 0) AS ReceivedQty
FROM
    (
        SELECT Customer, SUM([Available Quantity]) AS AvailableQty
        FROM Stock
        GROUP BY Customer
    ) s
FULL OUTER JOIN
    (
        SELECT Customer, SUM([Shipped Quantity]) AS ShippedQty
        FROM Dispatch
        GROUP BY Customer
    ) d ON s.Customer = d.Customer
FULL OUTER JOIN
    (
        SELECT Customer, SUM([Received Quantity]) AS ReceivedQty
        FROM Receipt
        GROUP BY Customer
    ) r ON COALESCE(s.Customer, d.Customer) = r.Customer

关键说明： #

• 子查询先做单表聚合：每张表单独计算对应数量的总和，确保每个Customer仅返回一条记录，避免多表关联时的重复计算。
• FULL OUTER JOIN覆盖所有客户：即使某客户只在其中一张表存在，也能被查询到；如果只需要保留Stock表中的客户，可将FULL OUTER JOIN改为LEFT JOIN。
• COALESCE和ISNULL处理空值：把客户在部分表中无数据的NULL结果替换为0，让输出更直观。

如果你的需求是仅以Stock表的客户为基础关联另外两张表，可调整为：

SELECT
    s.Customer,
    s.AvailableQty,
    ISNULL(d.ShippedQty, 0) AS ShippedQty,
    ISNULL(r.ReceivedQty, 0) AS ReceivedQty
FROM
    (
        SELECT Customer, SUM([Available Quantity]) AS AvailableQty
        FROM Stock
        GROUP BY Customer
    ) s
LEFT JOIN
    (
        SELECT Customer, SUM([Shipped Quantity]) AS ShippedQty
        FROM Dispatch
        GROUP BY Customer
    ) d ON s.Customer = d.Customer
LEFT JOIN
    (
        SELECT Customer, SUM([Received Quantity]) AS ReceivedQty
        FROM Receipt
        GROUP BY Customer
    ) r ON s.Customer = r.Customer

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