SQL 查询以查找至少具有所列标签之一的文章

Question

我有 3 个表:文章、标签和 article_tag(连接它们的那个)。

我想找到所有包含查询中列出的部分(或全部)标签的文章，并按匹配标签的数量对它们进行排序。可能吗？

英文示例:

Query: Find me all articles that have at least one of these tags "jazz, bass guitar, soul, funk", order them by the number of matched tags.

Output:
Article 1 (jazz, bass-guitar, soul, funk)
Article 2 (jazz, bass-guitar, soul)
Article 3 (jazz, bass-guitar)
Article 4 (funk)

Answer 1

大概是这样的:

select articles.article_id, count(tags.tag_id) as num_tags, group_concat(distinct tag_name separator ',') as tags
from articles, article_tag, tags
where articles.tag_id = article_tag.tag_id
  and article_tag.tag_id = tags.tag_id
  and tags.tag_name in ( 'jazz', 'funk', 'soul', 'Britney Spears' )
group by articles.article_id
order by count(tags.tag_id) desc