怎样在SQL中通过嵌套子查询解决多对多关系的精确筛选-SQL-PHP中文网

直接用where...in查多对多会漏数据或错召，因为in子查询是独立存在性判断，无法保证同一用户在中间表中同时满足多个角色条件；正确做法是显式join中间表，再用group by+having count(distinct)精准控制匹配数量。

怎样在sql中通过嵌套子查询解决多对多关系的精确筛选

为什么直接用 `WHERE ... IN` 查多对多会漏数据或错召

因为 IN 子查询是“存在性判断”，不是“集合匹配”。比如查“同时有角色 1 和 2 的用户”，写成：

SELECT * FROM users 
WHERE id IN (SELECT user_id FROM user_role WHERE role_id = 1)
  AND id IN (SELECT user_id FROM user_role WHERE role_id = 2);

看似合理，但两个子查询各自独立执行，不保证同一个 user_id 在中间表里真有两条对应记录；更糟的是，如果中间表没索引，数据库可能走全表扫描，性能断崖式下跌。

结果可能为空（因优化器误判关联路径）
也可能重复返回同一用户多次（若中间表有冗余数据）
无法表达“且必须仅有这两个角色”的语义

用 `GROUP BY + HAVING` 做“至少满足”筛选

这是最常用、也最容易上手的替代方案，适合“查拥有 A 和 B（至少）的用户”这类需求。

关键点在于：把中间表显式 JOIN 进来，再按主表字段分组，用 HAVING COUNT(DISTINCT ...) 控制最小匹配数。

SELECT u.id, u.name 
FROM users u 
JOIN user_role ur ON u.id = ur.user_id 
WHERE ur.role_id IN (1, 2) 
GROUP BY u.id, u.name 
HAVING COUNT(DISTINCT ur.role_id) = 2;

必须加 DISTINCT，防止单一角色重复插入导致计数虚高
WHERE 先过滤掉无关角色，减少分组数据量
联合索引 (user_id, role_id) 能极大提升性能

用双重 `NOT EXISTS` 实现“完全匹配”（不多不少）

当业务要求“恰好拥有 a、b、c 三项，不能多也不能少”，GROUP BY + HAVING 不够用了——它只校验下限，不拦冗余项。

这时要用双重否定逻辑：外层 NOT EXISTS 确保目标项一个不缺；内层 NOT EXISTS 拦住任何非目标项。

SELECT DISTINCT c1.taskid 
FROM CHECKITEM c1 
WHERE NOT EXISTS (
  SELECT 1 FROM (SELECT 'a' AS code UNION ALL SELECT 'b' UNION ALL SELECT 'c') t 
  WHERE NOT EXISTS (
    SELECT 1 FROM CHECKITEM c2 
    WHERE c2.taskid = c1.taskid AND c2.cheitcode = t.code
  )
)
AND NOT EXISTS (
  SELECT 1 FROM CHECKITEM c3 
  WHERE c3.taskid = c1.taskid AND c3.cheitcode NOT IN ('a','b','c')
);

第一层 NOT EXISTS 构造目标集合，避免硬编码依赖字段顺序
第二层 NOT EXISTS 是关键，它把“含 d 项”的任务直接踢出
MySQL 8.0+ 可改用 VALUES ROW('a'), ROW('b'), ROW('c')，更简洁

别把中间表藏进子查询里

所有靠谱的多对多精确筛选，都要求中间表出现在 FROM 或 JOIN 链中，而不是只躲在 WHERE 后面。

常见错误写法：

SELECT * FROM users 
WHERE id IN (
  SELECT user_id FROM user_role 
  WHERE role_id IN (1, 2) 
  GROUP BY user_id 
  HAVING COUNT(DISTINCT role_id) = 2
);

问题在于：这个子查询仍没显式连接 users 表，数据库无法利用 user_id 上的索引做高效关联，执行计划常退化为嵌套循环 + 全表扫描。