MySQL关联5张表查询双SUM列结果异常的问题求助

这个问题我太熟悉了——直接把所有表硬连接在一起会产生笛卡尔积，把你的聚合计算彻底搞乱！咱们一步步来修复：

问题根源 #

当你把inputs、warehouse、used这些表直接连接时，入库记录和已用记录会形成多对多的关联：比如某物料有2条入库记录和3条使用记录，连接后会生成2×3=6条重复记录。SUM()计算时会把入库量重复加3次、使用量重复加2次，结果自然错误。同时，内连接会过滤掉只有入库但没有使用记录的物料，所以你只得到了8条结果，漏掉了2条入库未使用的物料。

正确解法：先分别聚合，再关联 #

既然你已经有两个单独运行正确的查询，我们可以把它们作为子查询，分别计算每个物料的总入库和总使用量，再通过itemId和taskId左连接起来，这样既能保留所有入库物料，又能避免笛卡尔积导致的重复计算。

最终SQL语句 #

SELECT
    t.taskId,
    COALESCE(inputs_sub.itemId, used_sub.itemId) AS itemId,
    COALESCE(inputs_sub.itemName, used_sub.itemName) AS itemName,
    COALESCE(inputs_sub.total_input, 0) AS total_input,
    COALESCE(used_sub.total_used, 0) AS total_used
FROM task t
LEFT JOIN (
    -- 子查询：计算任务1的入库物料总量
    SELECT
        t_inner.taskId,
        it.itemId,
        it.itemName,
        SUM(ip.qty) AS total_input
    FROM items it
    JOIN inputs ip ON it.itemId = ip.itemId
    JOIN warehouse w ON ip.warehouseId = w.warehouseId
    JOIN task t_inner ON w.taskId = t_inner.taskId
    WHERE t_inner.taskId = 1
    GROUP BY it.itemId, t_inner.taskId, it.itemName
) inputs_sub ON t.taskId = inputs_sub.taskId
LEFT JOIN (
    -- 子查询：计算任务1的已用物料总量
    SELECT
        t_inner.taskId,
        it.itemId,
        it.itemName,
        SUM(u.qty) AS total_used
    FROM items it
    JOIN used u ON it.itemId = u.itemId
    JOIN task t_inner ON u.taskId = t_inner.taskId
    WHERE t_inner.taskId = 1
    GROUP BY it.itemId, t_inner.taskId, it.itemName
) used_sub ON t.taskId = used_sub.taskId 
           AND inputs_sub.itemId = used_sub.itemId
WHERE t.taskId = 1
ORDER BY itemId;

关键细节说明 #

• 子查询预聚合：先在子查询里完成SUM()计算，避免连接后产生重复记录导致的错误累加。
• LEFT JOIN：确保即使某个物料只有入库记录（没有使用），或者只有使用记录（没有入库，虽然你的场景里可能不存在，但更严谨），都会被包含在结果中。
• COALESCE函数：把NULL值替换为0，比如没有使用记录的物料，total_used会显示0而不是NULL，报表更友好。
• GROUP BY完整性：子查询里的GROUP BY包含了所有非聚合字段（taskId、itemId、itemName），符合SQL标准（避免某些数据库的模式兼容问题）。

这样运行后，你应该能得到10条正确的结果，每个物料的total_input和total_used都是准确的。

内容的提问来源于stack exchange，提问作者Jaime Eduardo Bedoya E