需要有关 sql 查询的帮助以查找具有最指定标签的内容 [英] Need help with sql query to find things with most specified tags

问题描述

假设我有以下表格:

id:整数
名称:字符串

id: integer
name: string

id:整数
正文:文本

id: integer
body: text

id:整数
tag_id:整数
post_id:整数

id: integer
tag_id: integer
post_id: integer

我将如何编写一个查询，按照包含以下标签数量最多的帖子(标签表的名称属性)的顺序选择所有帖子:奶酪"、葡萄酒"、巴黎"、Frace"", "城市", "风景", "艺术"

How would I go about writing a query that selects all posts in order of the post containing the highest number of the following tags (name attribute of tags table): "Cheese", "Wine", "Paris", "Frace", "City", "Scenic", "Art"

另请参见:需要 sql 查询帮助以查找所有指定标记的内容标签(注意:相似，但不能重复！)

See also: Need help with sql query to find things tagged with all specified tags (note: similar, but not a duplicate!)

推荐答案

与您链接的问题不同，您没有在此处指定需要匹配所有标签.此查询适用于任何.

Unlike your linked question, you did not specify here that you needed to match ALL tags. This query works for ANY.

SELECT p.id, p.text, count(tg.id) as TagCount
    FROM Posts p 
        INNER JOIN Taggings tg 
            ON p.id = tg.post_id
        INNER JOIN Tags t 
            ON tg.tag_id = t.id
    WHERE t.name in ('Cheese', 'Wine', 'Paris', 'Frace', 'City', 'Scenic', 'Art')
    GROUP BY p.id, p.text
    ORDER BY TagCount DESC

这篇关于需要有关 sql 查询的帮助以查找具有最指定标签的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！