SQL查询进阶：如何用IN、EXISTS与GROUP BY实现“同时使用”逻辑？

如果你在数据库实验课或者面试题里刷到过“供应商-零件-工程”这套经典练习对spj这个表名一定不陌生。最近就有个同学拿了道题来问我怎么查出“同时使用红色的螺母零件和蓝色的螺丝刀零件的工程”。第一反应是这不就是个多表查询吗where后面多写几个条件就完事但真正下手才发现“同时”这两个字并不是多一个and那么简单。它的本质是集合求交集牵出来的是一连串SQL知识关联表、IN子查询、EXISTS关联子查询、自连接、GROUP BY和HAVING哪一环没想明白都可能写错。这篇文章我准备从数据模型拆到SQL写法再聊索引和慢查询把这题彻底讲透。适合正在补SQL基础的学生也适合整天跟统计报表打交道的开发。1. 先把题目数据模型吃透spj并不是三个字母那么简单1.1 表结构和业务含义经典教材里这个场景通常有供应商、零件、工程以及它们之间的供应关系命名习惯是s供应商表字段一般是sno, sname, status, cityp零件表字段一般是pno, pname, color, weightj工程表字段一般是jno, jname, cityspj供应关系表字段一般是sno, pno, jno, qty表示某个供应商给某个工程供应了多少某种零件。建表SQL可以这么写CREATE TABLE s ( sno CHAR(4) PRIMARY KEY, sname VARCHAR(32), status INT, city VARCHAR(32) ); CREATE TABLE p ( pno CHAR(4) PRIMARY KEY, pname VARCHAR(32), color VARCHAR(16), weight DECIMAL(10,2) ); CREATE TABLE j ( jno CHAR(4) PRIMARY KEY, jname VARCHAR(32), city VARCHAR(32) ); CREATE TABLE spj ( sno CHAR(4), pno CHAR(4), jno CHAR(4), qty INT, PRIMARY KEY (sno, pno, jno), FOREIGN KEY (sno) REFERENCES s(sno), FOREIGN KEY (pno) REFERENCES p(pno), FOREIGN KEY (jno) REFERENCES j(jno) );这里有几个值得注意的点。第一spj表的主键是(sno, pno, jno)意味着同一个供应商给同一个工程供应同一种零件时只保留一条汇总数量。实际企业中如果采购单有批次往往还要加一个批次字段这个先不展开。第二这道题里“红色的螺母零件”和“蓝色的螺丝刀零件”说明过滤条件落在p.pname和p.color上而工程号落在spj.jno上两者之间隔着一张关系表。1.2 为什么“同时使用”不能靠where and直接写很多人第一版SQL容易写成这样SELECT spj.jno FROM spj, p WHERE spj.pno p.pno AND p.pname 螺母 AND p.color 红 AND p.pname 螺丝刀 AND p.color 蓝;这句一眼看去条件都写了但逻辑上有两个致命问题。第一个问题是同一行记录里的p.pname不可能既是“螺母”又是“螺丝刀”所以AND p.pname 螺母 AND p.pname 螺丝刀永远为假最终什么数据都查不出来。第二个问题是即使你把条件改成OR查出来的也不是“同时使用”而是“使用过其中任意一种零件”的工程语义就变了。“同时使用”在集合论里是交集先用红色螺母过滤出一批工程号再用蓝色螺丝刀过滤出一批工程号最后取两边都有的工程。SQL虽然不像集合语言那么直白但完全可以通过子查询、关联查询或分组聚合把交集表达出来。这也是为什么这道题看起来不难却值得认真拆一遍的原因。2. 最顺手的解法两层IN子查询2.1 先定位目标零件编号第一步不是直接查工程而是先弄清楚“红色螺母”和“蓝色螺丝刀”在零件表里到底对应哪些pno。假如业务上存在多条记录比如不同供应商给同一种零件编了不同物料号那pno可能是多个值不能默认只有一个。SELECT pno FROM p WHERE pname 螺母 AND color 红;实际做的时候可以用SELECT * FROM p;先看一眼表里有哪些零件避免字段名搞错。以前见过有人把color写成colour或者把表里的pname记成name一执行就报错白白浪费时间。2.2 把求交集写成IN嵌套拿到两个零件编号集合后可以用两层IN子查询把“同时使用”变成“工程号既在红色螺母集合里也在蓝色螺丝刀集合里”SELECT jno FROM j WHERE jno IN ( SELECT jno FROM spj WHERE pno IN ( SELECT pno FROM p WHERE pname 螺母 AND color 红 ) ) AND jno IN ( SELECT jno FROM spj WHERE pno IN ( SELECT pno FROM p WHERE pname 螺丝刀 AND color 蓝 ) );这条SQL的可读性很好就算让刚学SQL的同事接手也能一眼看出这是两个集合取交集。这里有个细节要注意如果spj表里同一个工程对同一种零件有好几条供应记录SELECT jno FROM spj WHERE ...会产生重复工程号。但在外层IN判断中重复值不影响jno IN (...)的结果因为IN本质上是一个“存在性”判断。所以这个写法里不写DISTINCT也没有逻辑问题如果在意输出结果整洁可以在最外层加DISTINCT。2.3 返回工程名称的完整SQL上面查出来的是工程编号实际报表里一般还要工程名称。可以让最外层FROM j直接带出SELECT DISTINCT j.jno, j.jname FROM j WHERE j.jno IN ( SELECT jno FROM spj WHERE pno IN ( SELECT pno FROM p WHERE pname 螺母 AND color 红 ) ) AND j.jno IN ( SELECT jno FROM spj WHERE pno IN ( SELECT pno FROM p WHERE pname 螺丝刀 AND color 蓝 ) );这样返回结果就直接是工程编号和名称。2.4 写这种子查询最容易掉的三个坑第一个坑是把两个IN条件用OR连起来。上面说过OR表示两个条件满足其一即可查的是“使用过红色螺母或者蓝色螺丝刀”的工程不是“同时”。第二个坑是忽略最外层表的别名。有人习惯在子查询里引用j.jno但外层如果没起别名或者子查询里也有一张j表就会产生歧义MySQL报错时提示通常很友好但在多表长长的SQL里肉眼找起来仍然费劲。我的习惯是所有表一律起简短别名比如j AS je、spj AS sp否则后面加字段时容易串表。第三个坑是不清楚IN在子查询结果包含NULL时会不会有问题。jno IN (NULL)的结果是UNKNOWN这一行会被过滤掉如果集合里有正常值也有NULLNULL部分不会匹配任何jno但也不会导致整体查询报错。真正需要小心的是 NOT IN一旦子查询结果里有NULLNOT IN 的结果会莫名其妙变成空集这是SQL里一个很容易让人懵的行为。这道题用的是IN不太会踩到但如果把题目改成“找出没有使用过红色螺母的工程”就一定要避开NOT IN或先过滤掉NULL。3. EXISTS关联子查询执行思路完全不一样3.1 EXISTS写法用EXISTS写出来的核心思路是对外层工程表的每一行去检查是否存在一条满足条件的供应记录。SELECT je.jno, je.jname FROM j je WHERE EXISTS ( SELECT 1 FROM spj sp JOIN p p1 ON sp.pno p1.pno WHERE sp.jno je.jno AND p1.pname 螺母 AND p1.color 红 ) AND EXISTS ( SELECT 1 FROM spj sp JOIN p p2 ON sp.pno p2.pno WHERE sp.jno je.jno AND p2.pname 螺丝刀 AND p2.color 蓝 );这条SQL的特点是外层是工程表每遍历一个工程号就分别到供应关系和零件表里去“验一下存在性”。两个EXISTS之间用AND连接自然就是同时满足两个条件。整个逻辑和IN方案完全等价但执行策略有差异。3.2 为什么子查询里SELECT 1而不是SELECT *很多教程里写EXISTS都会用SELECT 1实际上MySQL执行EXISTS时根本不关心select列表写SELECT *也不会带来额外开销只是显得不专业。我写SELECT 1主要是为了提醒自己和看代码的人“这里只关心有没有记录不关心具体值”。有些数据库优化器还会忽略select list所以从性能角度两个写法没有本质区别但从代码语义上SELECT 1更干净。3.3 IN和EXISTS不能靠死记结论网上经常能看到“小表驱动大表用EXISTS大表驱动小表用IN”之类的经验法则但这句话放在MySQL里并不总是成立。MySQL的优化器会把IN子查询改写成semi-join也就是半连接并不像教科书理解的那样真的逐行执行子查询。不同的数据分布、索引情况、MySQL版本都会导致执行计划不一样。所以正确的做法只有一个拿实际数据和实际SQL去EXPLAIN。比如这个题如果j表只有几千行spj表有上百万行spj(pno)和spj(jno)都建了索引EXISTS方案往往能快速命中索引反过来如果spj表很小IN方案可能更直接。我在调试这类查询时经常用这样一条命令看执行计划EXPLAIN SELECT je.jno, je.jname FROM j je WHERE EXISTS (...) AND EXISTS (...);重点看type列和rows列。type如果是ALL说明全表扫描rows如果特别大说明没有有效索引。慢查询日志配合EXPLAIN使用是排查复杂SQL性能问题最实用的一组手段。4. 自连接加GROUP BY一眼看出数据重复的坑4.1 自连接为什么会让结果翻倍除了子查询还有一类很常见的思路是把spj表当作两张独立的表来连接一张代表红色螺母供应记录另一张代表蓝色螺丝刀供应记录SELECT a.jno FROM spj a JOIN p p1 ON a.pno p1.pno AND p1.pname 螺母 AND p1.color 红 JOIN spj b ON a.jno b.jno JOIN p p2 ON b.pno p2.pno AND p2.pname 螺丝刀 AND p2.color 蓝;这个SQL的结果本身没问题问题在于大量重复。假设工程E1在红色螺母上有3条供应记录在蓝色螺丝刀上有2条供应记录那么a.jno b.jno会把3条和2条做笛卡尔积生成6行相同结果。如果直接把这段SQL接出去做统计比如求数量就会严重失真。这也是很多人在自连接时最容易栽的跟头。4.2 用GROUP BY HAVING COUNT(DISTINCT ...) 解决更稳的写法是先只按工程分组再统计满足条件的零件种类数SELECT sp.jno FROM spj sp JOIN p ON sp.pno p.pno WHERE (p.pname 螺母 AND p.color 红) OR (p.pname 螺丝刀 AND p.color 蓝) GROUP BY sp.jno HAVING COUNT(DISTINCT p.pno) 2;这个写法的逻辑是先把所有涉及红色螺母或蓝色螺丝刀的供应记录都捞出来按工程分组然后统计每个工程命中了多少个不同的零件编号。只有命中了2个不同pno的工程才是同时使用了这两种零件的工程。COUNT里的DISTINCT很重要。如果同一个工程在红色螺母上有5条记录但这5条记录的pno是同一个COUNT(pno)会数出5个最后只有1个不同值用COUNT(DISTINCT p.pno)才能正确判断。4.3 WHERE里的OR优先级容易现场翻车上面WHERE里我特意加了括号。SQL中AND的优先级高于OR如果不加括号条件会变成(p.pname螺母 AND p.color红 AND p.pname螺丝刀) OR (p.color蓝)语义完全不对。这种括号问题在实际开发中特别常见代码review时总能看到有人把多个过滤条件用OR一拉到底结果查出来的数据一会儿多一会儿少。一个实用习惯是只要有OR就用括号把每个“业务条件组”包起来哪怕你觉得优先级很熟也给后来阅读的人减少负担。5. 推广到N种零件和真实项目落地5.1 把两道零件条件变成N道如果题目从两种零件变成三种、五种IN方案和EXISTS方案会越写越长GROUP BY方案反而更有优势。例如要查询“同时使用红螺母、蓝螺丝刀、绿扳手”的工程SELECT sp.jno FROM spj sp JOIN p ON sp.pno p.pno WHERE (p.pname 螺母 AND p.color 红) OR (p.pname 螺丝刀 AND p.color 蓝) OR (p.pname 扳手 AND p.color 绿) GROUP BY sp.jno HAVING COUNT(DISTINCT p.pno) 3;只要把零件列表写进WHERE的OR条件组再把HAVING里的数字改成目标数量就能通用。但如果零件条件来自一张配置表还可以写得更加动态。比如有一张target_part表存着目标零件名称和颜色SQL就能写成SELECT sp.jno FROM spj sp JOIN p ON sp.pno p.pno JOIN target_part t ON p.pname t.pname AND p.color t.color GROUP BY sp.jno HAVING COUNT(DISTINCT p.pno) (SELECT COUNT(*) FROM target_part);这种写法在报表系统里很有用需求方改配置表就能跑出新结果不用频繁改SQL。5.2 用MyBatis-Plus写这类查询的注意点真实项目中这类SQL很少直接裸写在代码里一般会交给ORM框架。以MyBatis-Plus为例很多人习惯用QueryWrapper拼条件但遇到这种“存在性”和“集合交集”语义过硬拼Wrapper容易拼出语义不对的SQL。我的做法通常是分两步走第一步先查“使用红色螺母的工程集合”第二步再查“使用蓝色螺丝刀的工程集合”然后在业务代码里对两个集合求交集。数据量不大时代码可读性比一条超级SQL高很多。如果用MyBatis-Plus且表里有逻辑删除字段比如deleted记得所有查询都要带上deleted 0。以前我排查过一个统计异常SQL手工执行是对的应用里查出来少了几行最后发现是MyBatis-Plus的自动逻辑删除拦截器在某些聚合子查询里没有按预期拼接条件导致统计基数不对。这类坑不亲自踩一遍很难从文档里看出来。5.3 如果还要带分页和排序查询结果如果工程很多业务端通常要分页。MySQL里常规写法是加LIMIT offset, size但分页前必须有一个稳定排序否则翻页时数据会乱跳。例如SELECT DISTINCT je.jno, je.jname FROM j je WHERE je.jno IN (...) AND je.jno IN (...) ORDER BY je.jno LIMIT 0, 20;ORDER BY字段最好是主键或唯一键不要只按名称排序。工程名称如果存在重名翻页时很容易出现记录串页。Oracle里分页用OFFSET ... FETCH NEXT ... ROWS ONLY或者ROWNUMSQL Server用OFFSET ... FETCH不同数据库语法差异不小迁移时要注意。6. 性能复盘索引、执行计划和慢查询6.1 三种方案的执行计划对比在我本地构建的测试数据里spj大概30万行j表1万行p表几百行我用MySQL 8.0分别跑了IN、EXISTS、GROUP BY三种写法得到的结果集完全一致但执行方式不同方案执行思路最关键的表性能瓶颈点两层IN先算子查询集合再做外层IN判断spj、ppno和jno是否走索引EXISTS关联子查询外层工程表逐行检查存在性j、spj、pspj(jno)索引质量自连接GROUP BY先把目标零件记录找出来再分组统计spj、p分组前扫描范围在我的测试环境里p表很小所以三种方案都很快但如果把spj涨到几百万行EXISTS方案在内层关联spj时能否用到spj(jno)索引就直接决定了查询要不要跑十几秒。6.2 索引怎么建才有用这道题的核心访问路径是“从零件条件出发找到spj相关记录再关联工程”。针对这个场景索引建议很明确p表建(pname, color)联合索引。因为过滤条件总是先按零件名称和颜色筛spj表建pno和jno两个单列索引。如果发现经常有按工程聚合统计的需求可以考虑(jno, pno)联合索引j表按主键jno查询即可一般不用额外设计。联合索引的顺序有讲究。如果查询经常把pname放在前面就把pname放联合索引第一列如果color的区分度更高也可以考虑(color, pname)但通常名称加颜色的组合查询前缀匹配的多样性已经足够。6.3 视图真的能加快查询吗热词里有人问“视图可以加快查询速度吗”这是一个常见误区。普通视图只是一条保存起来的SQL定义查询视图时数据库还是要执行内部语句不会自动把结果存下来。把复杂查询包成视图最大的好处是方便复用和权限控制而不是性能提升。如果确实想加快这类“同时使用多种零件”的查询速度合适的做法是提前把“工程-零件颜色”的维度冗余到一张汇总表比如按工程每日统计使用的零件颜色集合应用端直接查汇总表。MySQL没有原生物化视图可以用定时任务或应用层异步更新汇总表本质上是用空间换时间。我在真实项目里就维护过一张类似的汇总表每天晚上把供应明细roll up成“工程零件维度”的宽表第二天所有统计报表都走这张表几百行SQL变成几十行查询时间从十几秒降到几百毫秒。这个思路比任何SQL调优都见效快。写到最后想分享一个我自己的体会。刚开始做这个题目的时候我也喜欢背“IN快还是EXISTS快”“先过滤哪个表”之类的口诀结果换一个数据分布就翻车。后来养成一个习惯每次写完SQL先看执行计划再用慢查询日志验证真实耗时逐步积累自己业务场景下的经验。对于“同时使用各种零件”这类存在性判断我现在的默认选项是条件少用IN条件多用GROUP BYHAVING能走索引一定走索引最后用EXPLAIN收尾。按照这个套路来不光这道题单位里那些带“都必须满足”字眼的报表SQL你也能一眼就看穿该怎么写。