mysql - 如何在 MySQL 左连接中对列求和

Question

所以我有 table cont_selling

---------------------------------
cont_selling_id   |     date    | 
---------------------------------
     1            | 2015-05-24  | 
     2            | 2015-06-06  | 
---------------------------------

表 02 cont_sold

   ----------------------------------------------------
    cont_sold_id |    cont_selling_id   |     price   | 
    ---------------------------------------------------
      1          |         1            |       10    | 
      2          |         1            |       10    | 
      3          |         1            |       30    | 
      4          |         2            |       20    | 
      5          |         2            |       10    | 
    --------------------------------------------------

和表03付款

 ----------------------------------------------
payment_id   |  cont_selling_id |      paid   | 
-----------------------------------------------
    1        |         1        |       10    | 
    2        |         2        |       10    | 
    3        |         1        |       20    | 
    4        |         1        |       10    | 
    5        |         2        |       10    | 
-----------------------------------------------

现在我需要基于 SELECT 表

现在我想根据cont_selling 表cont_selling_id 列合并所有这三个表

并且想对cont_sold表的price列和payment表的paid列求和

这就是我想要做的

期待输出

---------------------------------------------
cont_selling_id  |    price    |      paid   | 
---------------------------------------------
       1         |      50     |       40    | 
       2         |      30     |       20    | 
---------------------------------------------

所以我在 mysql 查询中这样尝试，但它给出了错误的总和结果

                    SELECT 

                    SUM(Z.price) as total, 
                    SUM(P.amount) as paid

                    FROM cont_selling S

                    LEFT JOIN cont_sold Z
                    ON S.cont_selling_id = Z.cont_selling_id

                    LEFT JOIN payment P
                    ON S.cont_selling_id = P.cont_selling_id    

                    GROUP BY S.cont_selling_id

对于上面的查询，我得到这样的输出

---------------------------------------------
cont_selling_id  |    price    |      paid   | 
---------------------------------------------
       1         |      150    |       40    | 
       2         |      60     |      120    | 
---------------------------------------------

Answer 1

在这里你可以如何使用 aggegare 部分进入内部查询然后加入

select 
cs.cont_selling_id,
price,
paid
from cont_selling cs
left join(
 select sum(price) as price , cont_selling_id from cont_sold
 group by cont_selling_id
)x on x.cont_selling_id = cs.cont_selling_id,
left join(
 select sum(paid) as paid , cont_selling_id from payment
 group by cont_selling_id
)y
on y.cont_selling_id = cs.cont_selling_id;