MYSQL - 将 SUM 与 JOIN 结合使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14773557/
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
MYSQL - Using SUM with JOIN
提问by Jimmery
Ok, so I have 4 tables.
好的，所以我有 4 张桌子。
A userstable with columns idand name.
users包含列id和的表name。
A groupstable with columns id, nameand owner.
一个groups与列的表id，name和owner。
A itemstable with columns groupand content.
items包含列group和的表content。
A contenttable with columns id, nameand duration.
一个content与列的表id，name和duration。
Each user can have several groups. Each group can have several items inside it. Each item represents one of the pieces of content.
每个用户可以有多个组。每个组中可以有多个项目。每个项目代表一个内容片段。
I want to be able to list all the groups, with a sum of all durations of each piece of content inside that group.
我希望能够列出所有组，以及该组内每条内容的所有持续时间的总和。
What Ive been trying is this:
我一直在尝试的是这样的：
select groups.id,groups.name,users.name,sum(content.duration) as duration from groups
join users on groups.owner=users.id
join items on items.group=groups.id
join content on content.id=items.content
Unfortunately this only gives me one result, with a total sum of all the durations of each piece of content in all of the groups - like so:
不幸的是，这只给了我一个结果，所有组中每个内容的所有持续时间的总和 - 如下所示：
"g001", "Group 1", "Me", "400"
What I am expecting is something like:
我期待的是这样的：
"g001", "Group 1", "Me", "160"
"g002", "Group 2", "You", "160"
"g003", "Group 3", "Them", "80"
回答by echo_Me
try this
尝试这个
select groups.id,groups.name,users.name,sum(content.duration) as duration from groups
join users on groups.owner=users.id
join items on items.group=groups.id
join content on content.id=items.content
group by groups.name
回答by Hamlet Hakobyan
Try this:
尝试这个：
select groups.id,groups.name,users.name,sum(content.duration) as duration
from groups
join users
on groups.owner=users.id
join items
on items.group=groups.id
join content
on content.id=items.content
group by groups.id,groups.name,users.name
回答by John Woo
LEFT JOINallows you to display all groups even without content but the total value of the durationwill be zero.
LEFT JOIN即使没有内容，您也可以显示所有组，但 的总值duration将为零。
SELECT a.id, a.name GroupName,
d.name OwnerName,
SUM(c.duration) totals
FROM groups a
INNER JOIN users d
ON a.owner = d.id
LEFT JOIN items b
ON a.id = b.group
LEFT JOIN content c
ON b.content = c.id
GROUP BY a.id, a.name, d.name
