SQL查询多对多:查找给定集合中包含所有标签的所有项目

基础概念

在关系型数据库中，多对多关系通常通过一个中间表（也称为关联表或连接表）来实现。假设我们有两个表：projects 和 tags，它们之间的关系通过一个中间表 project_tags 来表示。

projects 表存储项目信息。
tags 表存储标签信息。
project_tags 表存储项目和标签之间的关联关系。

类型

多对多关系的类型主要体现在中间表的设计上：

简单关联表：仅包含两个外键，分别指向 projects 和 tags 表。
带额外信息的关联表：除了外键外，还可以包含其他信息，如关联时间等。

应用场景

多对多关系在许多实际应用中都有广泛的应用，例如：

博客系统：一个博客文章可以有多个标签，一个标签也可以对应多篇博客文章。
电商系统：一个商品可以有多个分类标签，一个分类标签也可以对应多个商品。

查询给定集合中包含所有标签的所有项目

假设我们有以下表结构：

-- projects 表
CREATE TABLE projects (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

-- tags 表
CREATE TABLE tags (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

-- project_tags 表
CREATE TABLE project_tags (
    project_id INT,
    tag_id INT,
    PRIMARY KEY (project_id, tag_id),
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (tag_id) REFERENCES tags(id)
);

假设我们要查找包含所有给定标签的所有项目，可以使用以下 SQL 查询：

SELECT p.id, p.name
FROM projects p
WHERE NOT EXISTS (
    SELECT 1
    FROM tags t
    WHERE t.name IN ('tag1', 'tag2', 'tag3') -- 给定的标签集合
      AND NOT EXISTS (
          SELECT 1
          FROM project_tags pt
          WHERE pt.project_id = p.id
            AND pt.tag_id = t.id
      )
);