MySQL与Group By一对多连接仅返回一个观察值 [英] MySQL one-to-many join with Group By only returns one observation

问题描述

我有一个注释表和一个标记表.对于每个评论，可能有多个标签，也可能没有.我想加入两者，以便获得每个评论的标签列表.

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.

评论表:

+---------+----------+---+
|CommentID|   Title  | ..|
+---------+----------+---+
|   1     |   animals|   |
|   2     |   plants |   |
+---------+----------+---+

TagTable:

+---------+----------+---+
|  TagID  |CommentID | ..|
+---------+----------+---+
|    5    |     1    |   |
|    6    |     1    |   |
|    7    |     3    |   |
+---------+----------+---+

因此，查询应返回标记(5,6)的CommentID == 1，并返回空数组以获取CommentID == 2

So, a query should return the tags, (5,6) for a commentID == 1 and empty array for CommentID == 2

这就是我所拥有的-它只会选择最后一个ID，而不是倍数:

This is what I have - it only selects the last ID and not multiples:

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

推荐答案

您可以使用

You can use GROUP_CONCAT to turn data in multiple rows into a single delimited string:

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

在这种情况下，如果注释没有相应的标记，则该字段将为NULL.

In this case, if a comment does not have a corresponding tag, the field would just be NULL.

SQLFiddle演示

这篇关于MySQL与Group By一对多连接仅返回一个观察值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！