mysql - LEFT JOIN - 列出所有 chefe 卖家及其销售额和值(value)

Question

在下面的演示示例中，我得到了每个卖家的销售额总和

tab_sellers:

id_seller | seller | chefe
   1         John     bart
   2         Marie    bart
   3         Jamie    lucas
   4         Arnold   lucas
   5         peter    Homer

tab_sales:

id | sales | seller | value
1     BMW       1      100
2     FORD      1      200
3     FIAT      2      300
4     FORD      2      400
5     FORD      3      400

然后我使用以下查询计算销售额:

SELECT tsl.sellers, 
       SUM(ifnull(ts.value,0)) AS value_sales, 
       COUNT(ts.sales) AS sales_number
FROM tab_sellers tsl
LEFT JOIN tab_sales ts ON  tsl.id_seller = ts.seller
GROUP BY tsl.seller

问题是我需要获得与每个厨师相关的销售额和值(value)的总和(该表是根据 tab_sellers 的示例构建的，我无法更改它)。

预期结果:

chefe | sales | value
 bart     4      1000
 lucas    1      400
 homer    0      0

Answer 1

你的方向是正确的，只要你愿意

get the sum of the sales and the values in relation to each chefe

然后使用 group by tab_sellers.chefe

 select tab_sellers.chefe, count(tab_sales.sales) as sales , coalesce(sum(tab_sales.value), 0) as summ
 from tab_sellers
 left join tab_sales
 on tab_sellers.id_seller = tab_sales.seller
 group by tab_sellers.chefe
 order by summ desc