mysql - 如何获得链接到第三个表的两个表的总和

Question

是否有任何 sql 向导可以帮助解决这个问题:假设我有 3 个表:

tbltype        tblvalue               tblcost
id  | type     id  | val | typeid     id  | cost| typeid
----------     ------------------     ------------------ 
1   | aaa      1   | 3   | 1          1   | 5   | 1 
2   | bbb      2   | 2   | 1          2   | 3   | 1
               3   | 2   | 2          3   | 1   | 2
               4   | 1   | 2          4   | 4   | 2

当我运行这个查询时:

SELECT t.type, SUM(val), SUM(cost)
FROM
    tbltype t 
    LEFT JOIN tblcost c ON (c.typeid = t.id)
    LEFT JOIN tblvalue v ON (v.typeid = t.id)
GROUP BY t.type;

我得到了错误的值

type | SUM(val) | SUM(cost)
---------------------------
aaa  | 10       | 16
bbb  | 6        | 10

我如何获得正确的值:

type | SUM(val) | SUM(cost)
---------------------------
aaa  | 5        | 8
bbb  | 3        | 5

为什么 sql 会有这样的行为？

Answer 1

要了解原因，请从查询中取出组和总和，然后查看它的总和:

SELECT t.type, val, cost
FROM
    tbltype t 
    LEFT JOIN tblcost c ON (c.typeid = t.id)
    LEFT JOIN tblvalue v ON (v.typeid = t.id)

您会看到输出中有来自 tblcost 和 tblvalue 的行的每种可能组合——这意味着当您对它们求和时，其中一些会被计算多次。

您需要分别聚合 tblcost 和 tblvalue。然后您可以将它们加入到 tbltype 中。加文的回答已经显示了一种方法。另一种方式是:

SELECT t.type, COALESCE(cost, 0) AS cost, COALESCE(val, 0) AS val
FROM tbltype t
     LEFT JOIN (SELECT SUM(cost) AS cost, typeid FROM tblcost GROUP BY typeid) tc
       ON tc.typeid = t.id
     LEFT JOIN (SELECT SUM(val) AS val, typeid FROM tblvalue GROUP BY typeid) tv
       ON tv.typeid = t.id

...根据您实际使用的数据库引擎，其性能可能会或可能不会不同(可能会更好，也可能不会更好)。