获取所有带有标签的GROUP_CONCAT字段的文章

问题描述

我有一个表articles，另一个tags和第三个名为article_tags的表.我想生成一个页面，其中列出了特定标签的所有文章.

I have a table articles, another tags, and a third called article_tags. I want to produce a page which lists all of the articles for a specific tag.

我的查询如下:

SELECT headline, GROUP_CONCAT(tags.tag_name) AS all_tags FROM articles
LEFT JOIN articles_tags ON articles.article_id = articles_tags.article_id
LEFT JOIN tags ON articles_tags.tag_id = tags.tag_id
WHERE tags.tag_name = 'japan'
GROUP BY articles.article_id

SELECT headline, GROUP_CONCAT(tags.tag_name) AS all_tags FROM articles
LEFT JOIN articles_tags ON articles.article_id = articles_tags.article_id
LEFT JOIN tags ON articles_tags.tag_id = tags.tag_id
WHERE tags.tag_name = 'japan'
GROUP BY articles.article_id

所有返回的文章都仅以japan作为标签，即使所涉及的文章具有多个标签也是如此.

All of the returned articles only have japan as a tag, even when the article in question has several tags.

这显然与WHERE子句有关，但是我无法在这里弄清楚该怎么做-理想情况下，我会得到一个像japan,china,korea这样的列表.这是子查询的地方吗?可以与SQL专家一起提出建议.

This is obviously related to the WHERE clause, but I can't figure out how to do what I want here - ideally I'd end up with a list like japan,china,korea instead. Is this the place for a subquery? Could do with a SQL guru to advise.

谢谢，马特

推荐答案

您至少可以使用两种方法.一种方法是与表连接两次.您指出的另一种方法是使用子查询.为了简单和易于阅读，我可能会在这里使用子查询.结果查询如下所示:

There are at least two approaches you could use. One approach is to join with the tables twice. The other as you point out is to use a subquery. For simiplicity and ease of reading, I'd probably go with the subquery here. The resulting query would look something like this:

SELECT
    headline,
    GROUP_CONCAT(tags.tag_name) AS all_tags
FROM articles
JOIN articles_tags ON articles.article_id = articles_tags.article_id
JOIN tags ON articles_tags.tag_id = tags.tag_id
WHERE articles.article_id IN (
    SELECT articles.article_id
    FROM articles
    JOIN articles_tags ON articles.article_id = articles_tags.article_id
    JOIN tags ON articles_tags.tag_id = tags.tag_id
    WHERE tags.tag_name = 'japan'
)
GROUP BY articles.article_id

这是使用更多JOIN的方法:

And here's the approach using more JOINs:

SELECT
    a.headline,
    GROUP_CONCAT(t2.tag_name) AS all_tags
FROM articles a
JOIN articles_tags at1 ON a.article_id = at1.article_id
JOIN tags t1 ON at1.tag_id = t1.tag_id AND t1.tag_name = 'tag1'
JOIN articles_tags at2 ON a.article_id = at2.article_id
JOIN tags t2 ON at2.tag_id = t2.tag_id
GROUP BY a.article_id;

这篇关于获取所有带有标签的GROUP_CONCAT字段的文章的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！