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

-- 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)
);

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
      )
);