mysql两张表一起计数_MySQL从第二个表中加入两个表计数和求和

如果我理解你的问题，你需要做的就是添加一个SUM()：

SELECT a.*,

Count(b.id) as counttotal,

sum(b.costs) TotalCost

FROM dealers a

LEFT JOIN inquiries b on a.id=b.dealer_id

GROUP BY a.id

ORDER BY name ASC我的建议是使用子查询来获取count和sum：

SELECT a.*,

b.countTotal,

b.TotalCosts

FROM dealers a

LEFT JOIN

(

select COUNT(ID) countTotal,

SUM(costs) TotalCosts,

dealer_id

from inquiries

group by dealer_id

) b

on a.id=b.dealer_id

ORDER BY name ASC我猜你从原始查询中使用的是MySQL。我建议使用子查询，因为MySQL使用GROUP BY的扩展，它允许选择列表中的项目是非聚合的，不包含在GROUP BY子句中。但是这会导致意外的结果，因为MySQL可以选择返回的值。 (见MySQL Extensions to GROUP BY)

来自MySQL Docs：

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. ... You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.