mysql GROUP_CONCAT DISTINCT 多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18845170/
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 GROUP_CONCAT DISTINCT multiple columns
提问by werva
I have a tag field for a blog posts. tags have unique id but their displayName might be duplicated. What I want is a query that selects posts and in all_tagsfield we get couples of (id,displayName) is this way:
我有一个博客文章的标签字段。标签具有唯一的 id，但它们的 displayName 可能重复。我想要的是一个选择帖子的查询，在all_tags字段中我们得到几个 (id,displayName) 是这样的：
id1,name1;id2,name2;id3,name3
My query looks like:
我的查询看起来像：
select ....
CONCAT_WS(';', DISTINCT (CONCAT_WS(',',tags.id,tags.displayName))) AS all_tags
Join ...post content ...
Join ...post_tags ...
Join ...tags ...
ORDER BY posts.id
This line causes problem:
此行导致问题：
CONCAT_WS(';', DISTINCT (CONCAT_WS(',',tags.id,tags.displayName))) AS all_tags
How should I modify it?
我应该如何修改它？
Some people use an inner (SELECT .. FROM)but as I have heard, it is so inefficien
有些人使用内部，(SELECT .. FROM)但据我所知，它非常低效
SELECT `posts`.*,`categories`.*,`creators`.*,`editors`.*
CONCAT_WS(';', DISTINCT GROUP_CONCAT(CONCAT_WS(',',tags.id,tags.displayName))) AS all_ids
FROM (`posts`)
LEFT JOIN `languages` ON `posts`.`language_id`=`languages`.`id`
LEFT JOIN `users` as creators ON `posts`.`creatorUser_id`=`creators`.`id`
LEFT JOIN `users` as editors ON `posts`.`lastEditorUser_id`=`editors`.`id`
LEFT JOIN `userProfiles` as editors_profile ON `editors`.`profile_id`=`editors_profile`.`id`
LEFT JOIN `categories` ON `posts`.`category_id`=`categories`.`id`
LEFT JOIN `postTags` ON `postTags`.`post_id`=`posts`.`id`
LEFT JOIN `tags` ON `postTags`.`tag_id`=`tags`.`id`
LEFT JOIN `postTags` as `nodetag_checks` ON `nodetag_checks`.`post_id`=`posts`.`id`
LEFT JOIN `tags` as `tag_checks` ON `nodetag_checks`.`tag_id`=`tag_checks`.`id`
WHERE ( 9 IN(`tag_checks`.`id`,`tag_checks`.`cached_parents`) OR 10 IN(`tag_checks`.`id`,`tag_checks`.`cached_parents`) OR 11 IN(`tag_checks`.`id`,`tag_checks`.`cached_parents`))
GROUP BY `posts`.`id` ORDER BY `posts`.`created` desc LIMIT 0, 20
回答by Stephan
Try this:
尝试这个：
GROUP_CONCAT(
DISTINCT CONCAT(tags.id,',',tags.displayName)
ORDER BY posts.id
SEPARATOR ';'
)
回答by Matthieu
As advised by @Willa, I add my comment as an anwser :
正如@Willa 所建议的，我将我的评论添加为 anwser ：
GROUP_CONCAT allows you to concat multiple fields :
GROUP_CONCAT 允许您连接多个字段：
GROUP_CONCAT(tags.id, ',', tags.displayName)
The only difference with Stephan's answer is in case your code allows the same tag to be affected several times to one post OR if you JOIN sequence leads you to multiselect the same record in the tag table. In those case, my solution will return the same tags multiple times.
与 Stephan 的答案的唯一区别是，如果您的代码允许同一个标签多次影响一个帖子，或者如果您 JOIN 序列导致您在标签表中多选相同的记录。在这种情况下，我的解决方案将多次返回相同的标签。
