MySQL 在 GROUP BY 之后加入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9390679/
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
LEFT JOIN after GROUP BY?
提问by Lem0n
I have a table of "Songs", "Songs_Tags" (relating songs with tags) and "Songs_Votes" (relating songs with boolean like/dislike).
我有一张包含“歌曲”、“Songs_Tags”（与标签相关的歌曲）和“Songs_Votes”（与布尔值喜欢/不喜欢的歌曲相关）的表格。
I need to retrieve the songs with a GROUP_CONCAT() of its tags and also the number of likes (true) and dislikes (false).
我需要检索带有 GROUP_CONCAT() 标签的歌曲以及喜欢 (true) 和不喜欢 (false) 的数量。
My query is something like that:
我的查询是这样的：
SELECT
s.*,
GROUP_CONCAT(st.id_tag) AS tags_ids,
COUNT(CASE WHEN v.vote=1 THEN 1 ELSE NULL END) as votesUp,
COUNT(CASE WHEN v.vote=0 THEN 1 ELSE NULL END) as votesDown,
FROM Songs s
LEFT JOIN Songs_Tags st ON (s.id = st.id_song)
LEFT JOIN Votes v ON (s.id=v.id_song)
GROUP BY s.id
ORDER BY id DESC
The problem is that when a Song has more than 1 tag, it gets returned more then once, so when I do the COUNT(), it returns more results.
问题是，当一首歌曲有超过 1 个标签时，它会返回不止一次，所以当我执行 COUNT() 时，它会返回更多结果。
The best solution I could think is if it would be possible to do the last LEFT JOIN after the GROUP BY (so now there would be only one entry for each song). Then I'd need another GROUP BY m.id.
我能想到的最佳解决方案是，是否可以在 GROUP BY 之后进行最后一个 LEFT JOIN（因此现在每首歌曲只有一个条目）。然后我需要另一个 GROUP BY m.id。
Is there a way to accomplish that? Do I need to use a subquery?
有没有办法做到这一点？我需要使用子查询吗？
采纳答案by Simon at My School Portal
There've been some good answers so far, but I would adopt a slightly different method quite similar to what you described originally
到目前为止已经有一些很好的答案，但我会采用一种与您最初描述的非常相似的稍微不同的方法
SELECT
songsWithTags.*,
COALESCE(SUM(v.vote),0) AS votesUp,
COALESCE(SUM(1-v.vote),0) AS votesDown
FROM (
SELECT
s.*,
COLLATE(GROUP_CONCAT(st.id_tag),'') AS tags_ids
FROM Songs s
LEFT JOIN Songs_Tags st
ON st.id_song = s.id
GROUP BY s.id
) AS songsWithTags
LEFT JOIN Votes v
ON songsWithTags.id = v.id_song
GROUP BY songsWithTags.id DESC
In this the subquery is responsible for collating songs with tags into a 1 row per song basis. This is then joined onto Votes afterwards. I also opted to simply sum up the v.votes column as you have indicated it is 1 or 0 and therefore a SUM(v.votes) will add up 1+1+1+0+0 = 3 out of 5 are upvotes, while SUM(1-v.vote) will sum 0+0+0+1+1 = 2 out of 5 are downvotes.
在此，子查询负责将带有标签的歌曲整理成每首歌曲的 1 行。然后将其加入到投票中。我还选择简单地总结 v.votes 列，因为您已经指出它是 1 或 0，因此 SUM(v.votes) 将加起来 1+1+1+0+0 = 5 个中的 3 个是赞成票，而 SUM(1-v.vote) 将求和 0+0+0+1+1 = 5 个中有 2 个是反对票。
If you had an index on votes with the columns (id_song,vote) then that index would be used for this so it wouldn't even hit the table. Likewise if you had an index on Songs_Tags with (id_song,id_tag) then that table wouldn't be hit by the query.
如果您有一个带有列 (id_song,vote) 的投票索引，那么该索引将用于此目的，因此它甚至不会命中表。同样，如果您在 Songs_Tags 上有一个带有 (id_song,id_tag) 的索引，那么该表将不会被查询命中。
editadded solution using count
使用计数编辑添加的解决方案
SELECT
songsWithTags.*,
COUNT(CASE WHEN v.vote=1 THEN 1 END) as votesUp,
COUNT(CASE WHEN v.vote=0 THEN 1 END) as votesDown
FROM (
SELECT
s.*,
COLLATE(GROUP_CONCAT(st.id_tag),'') AS tags_ids
FROM Songs s
LEFT JOIN Songs_Tags st
ON st.id_song = s.id
GROUP BY s.id
) AS songsWithTags
LEFT JOIN Votes v
ON songsWithTags.id = v.id_song
GROUP BY songsWithTags.id DESC
回答by My Other Me
Try this:
尝试这个：
SELECT
s.*,
GROUP_CONCAT(DISTINCT st.id_tag) AS tags_ids,
COUNT(DISTINCT CASE WHEN v.vote=1 THEN id_vote ELSE NULL END) AS votesUp,
COUNT(DISTINCT CASE WHEN v.vote=0 THEN id_vote ELSE NULL END) AS votesDown
FROM Songs s
LEFT JOIN Songs_Tags st ON (s.id = st.id_song)
LEFT JOIN Votes v ON (s.id=v.id_song)
GROUP BY s.id
ORDER BY id DESC
回答by ypercube??
Your code results in a mini-Cartesian product because you are doing two Joins in 1-to-manyrelationships and the 1table is on the same side of both joins.
您的代码会生成一个迷你笛卡尔积，因为您在1-to-many关系中执行两个连接，并且1表位于两个连接的同一侧。
Convert to 2 subqueries with groupings and then Join:
使用分组转换为 2 个子查询，然后加入：
SELECT
s.*,
COALESCE(st.tags_ids, '') AS tags_ids,
COALESCE(v.votesUp, 0) AS votesUp,
COALESCE(v.votesDown, 0) AS votesDown
FROM
Songs AS s
LEFT JOIN
( SELECT
id_song,
GROUP_CONCAT(id_tag) AS tags_ids
FROM Songs_Tags
GROUP BY id_song
) AS st
ON s.id = st.id_song
LEFT JOIN
( SELECT
id_song,
COUNT(CASE WHEN v.vote=1 THEN id_vote END) AS votesUp,
COUNT(CASE WHEN v.vote=0 THEN id_vote END) AS votesDown
FROM Votes
GROUP BY id_song
) AS v
ON s.id = v.id_song
ORDER BY s.id DESC
