MySQL SUM 与 3 个关系表

Question

我有 3 个表，其中包含以下列，它们是:

1. THead - HeadID(PK)、padcode、OKqty、TotNGqty
2. TDetail - HeadID(FK)、进程、名称、子代码
3. TSubDetail - 子代码 (FK)、NGcode、NGqty

这3个表的关系是这样的:THhead - TDetail - TSubDetail。

我希望下面的图片能够清楚地展示它们。

这是我的 MySQL 查询:

SELECT
THead.padcode,
SUM(THead.okqty) AS OK,
SUM(THead.TotNGqty) AS TOTALNG,
SUM(If(TSubDetail.NGCode = 'L3', NGqty, 0)) AS L3

FROM(THead JOIN TDetail ON THead.HeadID = TDetail.HeadID)
                        JOIN TSubDetail ON TDetail.Subcode=TSubDetail.Subcode

GROUP BY padcode

我想对 OK Qty 和 TotNGqty 求和，而不添加来自相同 TSubDetail.Subcode 的相同值。我尝试过 DISTINCT，但这不是解决方案。

从图像中您可以看到黄色突出显示的示例:

对于 Padcode = 'KVBS-B'

I want to get the OK Qty = 2845 and the Sum NG from Table Head = 705.

But from the query above, i got the wrong result, OK = 3245 and NG = 905.

我知道来自同一个 TSubDetail.Subcode 的相同值被添加了多次。但是正确的查询是什么才能得到真实的结果？

我希望有人可以帮助我并解释什么是正确的查询。预先感谢您对我的帮助。

Answer 1

在 SELECT 子句中使用相关子查询。这样，okqty 和 TotNGqty 就不会乘以 TSubDetail 中的相关行数。

SELECT
    THead.padcode,
    SUM(THead.okqty) AS OK,
    SUM(THead.TotNGqty) AS TOTALNG,
    SUM((
        SELECT SUM(TSubDetail.NGqty)
        FROM TDetail
        JOIN TSubDetail ON TDetail.Subcode=TSubDetail.Subcode
        WHERE THead.HeadID = TDetail.HeadID
          AND TSubDetail.NGCode = 'L3'
    )) AS L3 
FROM THead
GROUP BY padcode

或者聚合两次。首先在派生表中获取 SUM(TSubDetail.NGqty)。然后将其与外部查询中的 THead 联接起来:

SELECT
    THead.padcode,
    SUM(THead.okqty) AS OK,
    SUM(THead.TotNGqty) AS TOTALNG,
    SUM(x.L3) as L3
FROM THead
LEFT JOIN(
    SELECT TDetail.HeadID, SUM(TSubDetail.NGqty) as L3
    FROM TDetail
    JOIN TSubDetail ON TDetail.Subcode=TSubDetail.Subcode
    WHERE TSubDetail.NGCode = 'L3'
    GROUP BY TDetail.HeadID
) x ON x.HeadID = THead.HeadID
GROUP BY THead.padcode