SQL多表LEFT JOIN后出现重复记录 求和结果错误如何解决

你当前语句的错误本质是多表关联产生的笛卡尔积导致聚合重复计算：当某部门同时在Basic和LowPrice表存在多条记录时，两张表直接左连部门表会生成 M*N 条组合数据（M为Basic表该部门记录数，N为LowPrice表该部门记录数），求和时每条记录都会被重复计算N/M次，最终结果翻倍。同时你的原代码还存在两处语法问题：求和逻辑缺少乘法运算符*、GROUPBY需加空格写为GROUP BY。

先分别对Basic、LowPrice表按department_id做预聚合，保证每个部门在两个子查询中仅返回1条汇总记录，再将预聚合后的结果关联到Departments表，就能避免重复计算。

SELECT 
    d.department_id,
    -- 空值转0，避免无记录时计算结果为空，非Access数据库可替换为对应空值处理函数（MySQL用IFNULL、SQL Server用ISNULL）
    Nz(b.PurchasedBasic, 0) AS PurchasedBasic,
    Nz(b.SoldBasic, 0) AS SoldBasic,
    Nz(b.DiffBasic, 0) AS DiffBasic,
    Nz(l.PurchasedLowPrice, 0) AS PurchasedLowPrice,
    Nz(l.SoldLowPrice, 0) AS SoldLowPrice,
    Nz(l.DiffLowPrice, 0) AS DiffLowPrice
FROM Departments d
-- 先聚合Basic表
LEFT JOIN (
    SELECT 
        department_id,
        Sum(purchased * price) AS PurchasedBasic,
        Sum(sold * price) AS SoldBasic,
        Sum(purchased * price) - Sum(sold * price) AS DiffBasic
    FROM Basic
    GROUP BY department_id
) b ON d.department_id = b.department_id
-- 再聚合LowPrice表
LEFT JOIN (
    SELECT 
        department_id,
        Sum(purchased * price) AS PurchasedLowPrice,
        Sum(sold * price) AS SoldLowPrice,
        Sum(purchased * price) - Sum(sold * price) AS DiffLowPrice
    FROM LowPrice
    GROUP BY department_id
) l ON d.department_id = l.department_id;

注：如果你的表中部门ID字段确实是你拼写的departmend_id（多了一个n），请同步替换代码中所有department_id为实际字段名。

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