mysql - 如何选择一组行的sum()和另一组的sum()

Question

我在这里创建了一个带有示例数据和期望结果的 SQLfiddle 演示 :( http://sqlfiddle.com/#!9/dfe73a/7 )

样本数据

-- table company
+--------+---------+
|   id   |   name  |
+--------+---------+
|   1    |   foo   |
|   2    |   bar   |
+--------+---------+

 -- table sales
+--------+---------------+-----------------+
|   id   |   company_id  |   total_amount  |
+--------+---------------+-----------------+
|   1    |       1       |      300.0      |
|   2    |       1       |      300.0      |
|   2    |       1       |      100.0      |
+--------+---------------+-----------------+

 -- table moves
+--------+---------------+-----------------+
|   id   |   company_id  |   balance_move  |
+--------+---------------+-----------------+
|   1    |       1       |      700.0      |
|   2    |       1       |     -300.0      |
|   2    |       1       |     -300.0      |
+--------+---------------+-----------------+

我需要选择每家公司及其总销售额和总余额变动的总和

期望的结果

+----+----------------------+---------------------+
| id |  total_amount_sum    |   balance_move_sum  |
+----+----------------------+---------------------+
| 1  |         700          |          100        |
+----+----------------------+---------------------+
| 2  |        (null)        |        (null)       |
+----+----------------------+---------------------+

我试过这个 SQL 查询

SELECT 
    company.id,
    sum(total_amount) total_amount_sum,
    sum(balance_move) balance_move_sum
FROM company
     LEFT JOIN sales ON company.id = sales.company_id 
     LEFT JOIN moves ON company.id = moves.company_id
GROUP BY company.id

但是 sum() 函数添加了所有来自连接的冗余值，这导致 2100 (700*3) 总金额和 300 ( 100*3) 为净余额

错误的 SQL 语句结果

+----+----------------------+---------------------+
| id |  total_amount_sum    |   balance_move_sum  |
+----+----------------------+---------------------+
| 1  |         2100         |          300        |
+----+----------------------+---------------------+
| 2  |        (null)        |        (null)       |
+----+----------------------+---------------------+

有没有可能达到我想要的结果？

Answer 1

您通过连接重复行。

公司:每个公司 1 行

售后加入:每个公司 3 行 (1x3)

Moves 加入后:每个公司 9 行 (3x3)

因此，您最终将 SUM 增加了三倍。

一种修复方法是使用派生表 like this ，它首先计算 SUM，然后将结果行 1 对 1 连接起来。

SELECT 
    company.id,
    total_amount_sum,
    balance_move_sum
FROM company
LEFT JOIN (SELECT SUM(total_amount) total_amount_sum, company_id
           FROM sales
           GROUP BY company_id
           ) sales ON company.id = sales.company_id 
LEFT JOIN (SELECT SUM(balance_move) balance_move_sum, company_id
           FROM moves
           GROUP BY company_id
           ) moves ON company.id = moves.company_id