MySQL连接两个表的计数和第二个表的总和

一尘不染

MySQL连接两个表的计数和第二个表的总和

sql

我有拖车桌：

具有某些字段和主键ID的经销商

和具有以下字段ID Dealer_id费用的查询

对于每个经销商，有几个项目需要查询，我必须计算它们并加总成本。现在，我只剩下这条语句了：

SELECT a.*, Count(b.id) as counttotal
FROM dealers a
LEFT JOIN inquiries b on a.id=b.dealer_id
GROUP BY a.id
ORDER BY name ASC

但我不知道如何将表b的费用总计给每个经销商。有人可以帮忙吗？提前致谢

阅读 113

2021-03-17

共1个答案

一尘不染

您可以使用两个子查询：

SELECT  a.*
      , (SELECT Count(b.id) FROM inquiries I1 WHERE I1.dealer_id = a.id) as counttotal
      , (SELECT SUM(b.cost) FROM inquiries I2 WHERE I2.dealer_id = a.id) as turnover
FROM dealers a
ORDER BY name ASC

或者

SELECT  a.*
     , COALESCE(T.counttotal, 0) as counttotal   -- use coalesce or equiv. to turn NULLs to 0
     , COALESCE(T.turnover, 0) as turnover       -- use coalesce or equiv. to turn NULLs to 0
 FROM dealers a
 LEFT OUTER JOIN (SELECT a.id, Count(b.id) as counttotal, SUM(b.cost) as turnover
               FROM dealers a1 
               INNER JOIN inquiries b ON a1.id = b.dealer_id
              GROUP BY a.id) T
         ON a.id = T.id
ORDER BY a.name

2021-03-17