MySQL:GROUP_CONCAT结果的条件? [英] MySQL: condition on result of GROUP_CONCAT?

查看:1201
本文介绍了MySQL: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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆