时间戳

首页 > 解决方案 > MySQL SUM with 3 relation tables

mysql - MySQL SUM with 3 relation tables

问题描述

I have 3 tables with the following columns, they are:

  1. THead - HeadID(PK), padcode, OKqty, TotNGqty
  2. TDetail - HeadID(FK), process, name, Subcode
  3. TSubDetail - Subcode(FK), NGcode, NGqty

The relations of the 3 tables are like this: THead - TDetail - TSubDetail.

I hope following images can show them clearly.

Tables with sample data

This is my MySQL Query:

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

I want to sum the OK Qty and TotNGqty without adding the same values from the same TSubDetail.Subcode. I have tried DISTINCT, but that was not a solution.

From the image you can see from the yellow highlight sample:

For 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.

I know that the same values from the same TSubDetail.Subcode was added multiple times. But what is the correct query so i can get the true result?

I hope someone can help me and explain what is the correct query. Thanks in advance for helping me.

标签： mysql

解决方案

您正在显示数据的子集，因此很难明确回答，但您的 GROUP BY 子句仅包含“padcode”列，而不包含 HeadID 列。

我假设在整个表格中，您有多个 HeadID 具有相同 padcode (KVBS-B) 的记录，而不仅仅是黄色显示的记录。结果它们也被求和了，但是你看不到是哪一个，因为 GROUP BY 太笼统了。

推荐阅读