gpt4 book ai didi

sql - MySQL 连接两个表从第二个表计数和求和

转载 作者:行者123 更新时间:2023-12-03 14:34:25 25 4
gpt4 key购买 nike

我有拖车:

经销商
带有一些字段和主键 id


查询
具有以下字段
身份证
经销商_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 的成本。有人可以帮忙吗?提前致谢

最佳答案

您可以使用两个子查询:

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

关于sql - MySQL 连接两个表从第二个表计数和求和,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/15387808/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com