MySQL:GROUP_CONCAT结果的条件? [英] MySQL: condition on result of GROUP_CONCAT?
问题描述
我有一个类似于以下内容的SQL设置:
I have an SQL setup akin to the following:
文章
- id(PK)
- 名称
标签
- id(PK)
- 标签
...以及第三个表记录了两者之间的关联,因为每篇文章可以有多个标签:
...and a third table logging associations between the two, since there can be multiple tags to each article:
ARTICLE_TAG_ASSOCS
ARTICLE_TAG_ASSOCS
- id(PK)
- article_id(FK)
- tag_id(FK)
问题:如何查找具有特定标签的文章?
我能想到的最好的方法是:
The best I could come up with was this:
SELECT
name, GROUP_CONCAT(CASE WHEN tag = 'some-tag' THEN tag ELSE NULL END) AS tags
FROM
articles, tags, article_tag_assocs
WHERE
articles.id = article_id && tags.id = tag_id && tags IS NOT NULL
GROUP BY
article_id
那是对的;如果文章没有标签"some-tag",则该列将在标签"列中显示为空值. 但是如何完全消除该行?
That's on the right lines; if an article doesn't have the tag "some-tag" then that column shows up with a null value in the "tags" column. But how can I eliminate that row completely?
自然,我尝试附加
&& tags NOT LIKE '%some-tag%'
...到我的WHERE
子句中,然后得知您不能在WHERE
子句中使用GROUP_CONCAT
别名.所以我尝试附加:
...to my WHERE
clause, before learning that you can't use GROUP_CONCAT
aliases in WHERE
clauses. So I tried appending:
HAVING tags IS NOT NULL;
...对于查询,结果相同,即MySQL表示无法识别标签"列.
...to the query, with the same result, i.e. MySQL says it doesn't recognise the column "tags".
任何帮助表示赞赏.
推荐答案
如何?
SELECT distinct a.name
FROM articles a join
article_tag_assocs ata
on a.id = ata.article_id join
tags t
on t.id = ata.tag_id
WHERE t.tag = 'some-tag';
它可以很直接地回答您的问题.
It answers your question quite directly.
请注意,这也引入了表别名.这些使查询更易于编写和阅读.
Note that this also introduces table aliases. These make the query easier to write and to read.
这篇关于MySQL:GROUP_CONCAT结果的条件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!