mysql - MySQL :(可选)Build Group Concat字段

我有一套文章。其中一些文章被分配了x个标签。
我想选择SELECT中指定格式的所有文章，如果文章还分配了任何数量的标记，请将它们放入单个GROUP_CONCAT字段中。
下面的SQL执行了我需要的所有操作，只是它只检索至少有一个标记的文章。
如何才能使标记要求是可选的？

SELECT d.entry_id AS entry_id
   , d.field_id_40 AS body
   , t.title AS title
   , t.url_title AS url_title
   , t.entry_date AS entry_date
   , GROUP_CONCAT(g.tag_name ORDER BY g.tag_name) AS tag
FROM exp_channel_data d
   , exp_category_posts c
   , exp_channel_titles t
   , exp_tagger_links l
   , exp_tagger g
WHERE t.YEAR > '2005'
   AND t.site_id = d.site_id
   AND d.site_id = 9
   AND ( c.cat_id = 95
      OR c.cat_id = 93
      OR c.cat_id = 64
      OR c.cat_id = 24
   )
   AND d.entry_id = t.entry_id
   AND t.entry_id = c.entry_id
   AND t.STATUS = 'open'
   AND l.tag_id = g.tag_id
   AND d.entry_id = l.entry_id
GROUP BY d.entry_id

最佳答案

切换连接以使用JOIN语法，这将为您提供答案。

SELECT d.entry_id AS entry_id
   , d.field_id_40 AS body
   , t.title AS title
   , t.url_title AS url_title
   , t.entry_date AS entry_date
   , GROUP_CONCAT(g.tag_name ORDER BY g.tag_name) AS tag
FROM exp_channel_data AS d
INNER JOIN exp_channel_titles AS t ON d.site_id = t.site_id
   AND d.entry_id = t.entry_id
INNER JOIN exp_category_posts AS c ON t.entry_id = c.entry_id
LEFT OUTER JOIN exp_tagger_links AS l ON d.entry_id = l.entry_id
LEFT OUTER JOIN exp_tagger AS g ON l.tag_id = g.tag_id
WHERE t.YEAR > '2005'
   AND d.site_id = 9
   AND c.cat_id IN (95, 93, 64, 24)
   AND t.STATUS = 'open'
GROUP BY d.entry_id

您可能还想查看A visual explanation of SQL joins.

关于mysql - MySQL :(可选)Build Group Concat字段，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8423002/