MySQL 一对多加入 Group By 只返回一个观察
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11807650/
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 one-to-many join with Group By only returns one observation
提问by Justin
I have a comment table and a tag table. For each comment, there could be multiple tags, or none. I want to join the two so I can get a list of tags for each comment.
我有一个评论表和一个标签表。对于每条评论，可能有多个标签，也可能没有。我想加入两者，以便我可以获得每个评论的标签列表。
CommentTable:
评论表：
+---------+----------+---+
|CommentID| Title | ..|
+---------+----------+---+
| 1 | animals| |
| 2 | plants | |
+---------+----------+---+
TagTable:
标签表：
+---------+----------+---+
| TagID |CommentID | ..|
+---------+----------+---+
| 5 | 1 | |
| 6 | 1 | |
| 7 | 3 | |
+---------+----------+---+
So, a query should return the tags, (5,6) for a commentID == 1 and empty array for CommentID == 2
因此，查询应该返回标签，(5,6) 用于 commentID == 1 和空数组用于 CommentID == 2
This is what I have - it only selects the last ID and not multiples:
这就是我所拥有的 - 它只选择最后一个 ID 而不是倍数：
SELECT c.CommentID, c.Title, t.TagID FROM Comment as c
LEFT OUTER JOIN Tag as t ON c.CommentID = t.CommentID
GROUP BY t.TagID
回答by Zane Bien
You can use GROUP_CONCATto turn data in multiple rows into a single delimited string:
您可以使用GROUP_CONCAT将多行中的数据转换为单个分隔字符串：
SELECT a.CommentID,
a.Title,
GROUP_CONCAT(b.TagID ORDER BY b.TagID) AS tags
FROM CommentTable a
LEFT JOIN TagTable b ON a.CommentID = b.CommentID
GROUP BY a.CommentID,
a.Title
In this case, if a comment does not have a corresponding tag, the field would just be NULL.
在这种情况下，如果评论没有相应的标签，则该字段将为 NULL。
回答by Joe G Joseph
try this:
尝试这个：
SELECT c.CommentID, c.Title, t.TagID FROM Comment as c
LEFT OUTER JOIN Tag as t ON c.CommentID = t.CommentID
edit1: If you want to return only one row per group as per the comment
编辑1：如果你想根据评论每组只返回一行
SELECT c.CommentID, c.Title,MAX(t.TagID )
FROM Comment as c
left OUTER JOIN TagTable as t ON c.CommentID = t.CommentID
GROUP BY c.CommentID, c.Title
回答by XN16
You don't need the group by for this situation:
对于这种情况，您不需要 group by：
SELECT c.CommentID, c.Title, t.TagID FROM Comment as c
LEFT OUTER JOIN Tag as t ON c.CommentID = t.CommentID
