mysql 1对多 group by,MySQL与Group By的一对多连接仅返回一个观察

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

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

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值