SUM and Differences SQL Tables

Question

我有3个表tblTaille

BNT       ref       Size
a         104545      25
c         304654      50
a         104545      70
c         704564      75

tbl尾点

BNT       ref       Size
a         104545      20
c         304654      0
a         104545      70
c         704564      75

tbl缺陷

BNT       ref       Size
a         104545      1
c         304654      50
c         704564      0

我想要这个：

BNT       ref       Size
a         104545      4
c         304654      0
c         704564      0

这意味着BNT和ref在tblTailePointage中相等，tblDefectue，tblTaille将其显示为单行，得到4，（25+70-（20+70+1）

我尝试了这个查询，但没有产生所需的结果：

SELECT ID_OF, Col, BNT, SUM(size1), SUM(size2), SUM(size3), SUM(size4), SUM(size5), SUM(size6), SUM(size7), SUM(size8), SUM(size9), SUM(size10), SUM(Total)
FROM tblTailleALL
GROUP BY ID_OF, Col, BNT 

Answer 1

您可以在三个表上运行UNION ALL，从最后两个表中选择带负号的size。之后，您可以按ref和btn进行分组，并将大小放在SUM()中，如下所示：

SELECT btn, ref, SUM(size) FROM (
    SELECT btn, ref, size FROM tblTaille
UNION ALL
    SELECT btn, ref, -size FROM tblTaillePointage
UNION ALL
    SELECT btn, ref, -size FROM tblDefectue
) all_data
GROUP BY btn, ref

这里有一个关于sqlfiddle的演示。

Answer 2

类似的东西？

  SELECT t.BNT, t.ref, SUM(t.size) - SUM(tp.size) - SUM (d.size) as Size
    FROM tblTaille t
    JOIN tblTaillePointage tp 
      ON t.BNT = tp.BNT 
     AND t.ref = tp.ref 
    JOIN tblDefectuetp d 
      ON t.BNT = d.BNT 
     AND t.ref = d.ref
GROUP BY t.BNT, t.ref