mysql - 连接 2 个表的准确结果

Question

表 A 列出了一个元素以及必须经过 4 个可能站点中每个站点的件数。

Item      To Cut      To Paste      To Laminate      To Ship
Box          9            9              5              9
Cart         1            0              10             10

表 B 列出了每个站点已处理的件数。

Item      Cut Done      Paste Done      Laminated      Shipped
Box          9            0                0             0
Box          0            9                0             0
Box          0            0                5             0
Box          0            0                0             9

当我加入他们时，它显示:

Item      To Cut      Cut Done      To Paste      Paste Done
Box          36          9             36             9

这是因为表 B 的工作方式。我怎样才能让它在一张表中准确显示待切割和已完成的总切割量，以便百分比正确？

我的查询是:

select a.item, a.to_cut, a.to_paste, a.to_laminate, a.to_ship, b.cut_done, b.paste_done, b.laminated, b.shipped

from ToDo a join Done b on a.item = b.item

Answer 1

您应该添加一个包含计算所得总和的子查询。

select tableA.Item, To_Cut, To_Paste, To_Laminate, To_Ship, d.Cut_Done, d.Paste_Done, d.Laminated, d.Shipped
from tableA 
inner join (select Item, 
                   sum(Cut_Done) as Cut_Done,
                   sum(Paste_Done) as Paste_Done,
                   sum(Laminated) as Laminated,
                   sum(Shipped) as Shipped
            from   tableB
            group by Item) d
on tableA.Item = d.Item;

| Item | To_Cut | To_Paste | To_Laminate | To_Ship | Cut_Done | Paste_Done | Laminated | Shipped |
|-----:|--------|----------|-------------|---------|----------|------------|-----------|---------|
| Box  | 9      | 9        | 5           | 9       | 9        | 9          | 5         | 0       |

Rextester here