tags - SQL 查询 : Selecting a row if it has all related options in a related table

Question

给定一个表定义:

Articles:
 art_id | name  
 -------|--------------
  1     | article1
  2     | article2
  3     | article3

Tags:
  tag_id | description
  -------|--------------
   1     | Scientific
   2     | Long
   3     | Short

article_tags:
  art_id | tag_id
  -------|---------
   1     | 1
   1     | 2
   2     | 1
   2     | 3
   3     | 1
   3     | 2
   3     | 3

问题是如何选择所有 的所有文章科学 和 短 ?

请注意，对于 [2..N) 标签组合应该是通用的......

谢谢您的帮助。

Answer 1

您可以使用以下查询来获取结果:

select a.art_id, a.name
from articles a
inner join article_tags at
  on a.art_id = at.art_id
inner join tags t
  on at.tag_id = t.tag_id
where t.description in ('Short', 'Scientific')  -- tags here
group by a.art_id, a.name
having count(distinct t.tag_id) = 2 -- total count of tags here

见 SQL Fiddle with Demo

或者可以这样写:

select a.art_id, a.name
from articles a
inner join article_tags at
  on a.art_id = at.art_id
inner join tags t
  on at.tag_id = t.tag_id
group by a.art_id, a.name
having 
  sum(case when t.description = 'Short' then 1 else 0 end) >= 1 and
  sum(case when t.description = 'Scientific' then 1 else 0 end) >= ;

见 SQL Fiddle with Demo .

如果你只想返回文章id，那么你可以查询 article_tag table :

select art_id
from article_tags
where tag_id in (1, 3)
group by art_id
having count(distinct tag_id) = 2

见 SQL Fiddle with Demo