MySQL 对左连接 SQL 求和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4782026/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sum on a left join SQL
提问by Zoborg
I have two tables I wish to join on lets say table a and table b. Table b has many rows to table a's, table b contains prices (effectively a shopping basket). So what I want is all records from table a and the sum of the price from table b. I have tried
我有两个我想加入的表，比如说表 a 和表 b。表 b 有很多行来表 a，表 b 包含价格（实际上是一个购物篮）。所以我想要的是表 a 中的所有记录和表 b 中的价格总和。我试过了
select a.*, sum(b.ach_sell) from bookings a
left join pricing_line b on b.bookings = a.id
However this obviously doesn't do as I wish, it ends up with the total sum of all ach_sell (so one record is returned). Would someone kindly offer me a solution which would help? right now I am doing it programatically and I am pretty sure it could be done in SQL?
然而，这显然不像我希望的那样做，它以所有 ach_sell 的总和结束（因此返回一条记录）。有人会为我提供一个有帮助的解决方案吗？现在我正在以编程方式进行并且我很确定它可以在 SQL 中完成？
回答by SWeko
Your direction is right, just add a group by clause to separate the a.id's, something like this:
您的方向是正确的，只需添加一个 group by 子句来分隔 a.id，如下所示：
select a.id, sum(b.ach_sell)
from bookings a
left join pricing_line b
on b.bookings = a.id
group by a.id
回答by Guffa
Group on the fields from the bookings table:
对预订表中的字段进行分组：
select a.this, a.that, sum(b.ach_sell)
from bookings a
left join pricing_line b on b.bookings = a.id
group by a.this, a.that
回答by Martin Smith
You need to GROUP BYsomething if you don't want just a single result. Possibly?
GROUP BY如果你不想要一个结果，你需要做一些事情。可能吗？
select a.*,
sum(b.ach_sell) as ach_sell
from bookings a
left join pricing_line b on b.bookings = a.id
GROUP BY a.id
Other RDBMSs would insist on you having the entire column list in the GROUP BYbut MySQL doesn't.
其他 RDBMS 会坚持要求您将整个列列表包含在 中，GROUP BY但 MySQL 没有。
回答by bluish
select a.*, sum(b.ach_sell) as sum_column
from bookings a
left join pricing_line b
on b.bookings = a.id
group by a.id
Every time you use funcions of aggregation (SUM, COUNT, MIN, MAX, AVG) you need to group by some other set of fields (tipically the id) on which you don't apply an aggregation function.
每次使用聚合函数 ( SUM, COUNT, MIN, MAX, AVG) 时，您都需要按其他一些未应用聚合函数的字段集（通常是 id）进行分组。
回答by Vito
If you have many columns in select.
如果您在选择中有很多列。
Like this:
像这样：
SELECT
co.checkOutOrderID,
com.companyCode,
sd.serviceDispatchCode,
cu.customerName,
com.companySimp,
ISNULL(SUM (sdlp.partsModelPrice * sdlp.partsModelNum), 0) AS total
FROM
checkoutorder co
LEFT JOIN
servicedispatchorder sd ON sd.serviceDispatchID = co.serviceDispatchID
LEFT JOIN
customer cu ON cu.customerID = co.customerID
LEFT JOIN
company com ON com.companyID = co.companyID
LEFT JOIN
servicedispatchlinkparts sdlp ON sdlp.serviceDispatchID = sd.serviceDispatchID
GROUP BY
sd.serviceDispatchID, co.checkOutOrderID,
com.companyCode, sd.serviceDispatchCode,
cu.customerName, com.companySimp
