SQL查询以选择属于多个类别的帖子 [英] SQL query to select posts belonging to multiple categories

问题描述

我正在编写类似于博客软件的Web应用程序. 共有以下三个表格

I am writing a web application similar to a blogging software. There are three tables as below

Posts Table: Post_id,Post_Text
Post_Tags Table: Post_id,Tag_id
Tags Table:Tag_id,Tag_name

在概念化SQL查询时会遇到困难，该SQL查询将返回在给定集中具有所有"标签的帖子.

I have a difficulty in conceptualizing a SQL query that will return posts that have 'all of the' tags in a given set.

推荐答案

这是使用GROUP BY和COUNT或加倍NOT EXISTS.

第一种方法的一个例子是.

An example of the first approach would be.

SELECT pt.Post_id, p.Post_Text
FROM Post_Tags pt
JOIN Posts p ON p.Post_id = pt.Post_id
WHERE pt.Tag_id IN (1,2,3)
GROUP BY pt.Post_id
HAVING COUNT(DISTINCT pt.Tag_id) = 3

这篇关于SQL查询以选择属于多个类别的帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！