MySQL - 带连接的 GROUP_CONCAT [英] MySQL - GROUP_CONCAT with joins

问题描述

我的问题与 MySQL 非常相似:是否有可能到 group_concat 多行?，但我无法理解 group concat 是如何工作的.

My question is pretty similar to MySQL: is it possible to group_concat multiple rows?, but I can't get my head around how the group concat is working.

我的实际数据集很难解释 - 我将使用一组简化的表和关系作为示例.

My actual dataset would be hard to explain - I'll use a simplified set of tables and relationships as an example.

说我有一张桌子 photos

id  filename
1   bob.jpg
2.  ken.png
3.  lisa.jpg
4.  jim.jpg
...

和一个表 tags

id  title
1   family
2   friends
3   school
4   california
...

还有一座名为photo_tags

photo  tag
1      2
1      3
1      4
2      1
3      1

我想要这样的后排:

photo_id  filename   tags
1         bob.jpg    2,3,4
2         ken.png    1
3         lisa.jpg   1
4         jim.jpg    (NULL)

或...

photo_id  filename   tags
1         bob.jpg    friends,school,california
2         ken.png    family
3         lisa.jpg   family
4         jim.jpg    (NULL)

我最初的猜测是这样的:

My initial guess was something like this:

SELECT photos.filename,
GROUP_CONCAT(photo_tags.tag) AS associated_tags
FROM photos
LEFT OUTER JOIN tags
ON tags.photo = photos.id
GROUP BY photo_tags.tag

但是(对于知道自己在做什么的人来说可能很明显)，这不起作用.在做了一些研究并参考上面的链接后，我尝试了各种排列:

But (probably obvious to someone who knows what they're doing), that didn't work. After doing some research, and referencing the link above, I tried various permutations of this:

SELECT 
  file_name,
  GROUP_CONCAT(photo_tag_ids) AS associated_tags
FROM (
  SELECT 
    photos.filename AS file_name,
    photo_tags.tag AS photo_tag_ids
  FROM  
    photos
    INNER JOIN photo_tags
         ON photo_tags.photo= photos.id
) whatever
GROUP BY file_name
ORDER BY file_name;

TYIA.

推荐答案

SELECT photos.filename,
       GROUP_CONCAT(photo_tags.tag) AS associated_tags
FROM photos
LEFT OUTER JOIN tags ON tags.photo = photos.id
GROUP BY photos.filename

您必须按每行唯一的列进行分组，并且在组中您可以构建其他列的 group_concat.因此，将您的 group by 更改为 photos.filename.

You have to group by a column that will be unique for every row and in the group you can build a group_concat of other columns. So change your group by to photos.filename.

这篇关于MySQL - 带连接的 GROUP_CONCAT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！