企业官网建设流程全解析

如果你在数据库实验课或者面试题里刷到过“供应商-零件-工程”这套经典练习，对spj这个表名一定不陌生。最近就有个同学拿了道题来问我：怎么查出“同时使用红色的螺母零件和蓝色的螺丝刀零件的工程”。第一反应是这不就是个多表查询吗，where后面多写几个条件就完事；但真正下手才发现，“同时”这两个字并不是多一个and那么简单。它的本质是集合求交集，牵出来的是一连串SQL知识：关联表、IN子查询、EXISTS关联子查询、自连接、GROUP BY和HAVING，哪一环没想明白都可能写错。这篇文章我准备从数据模型拆到SQL写法，再聊索引和慢查询，把这题彻底讲透。适合正在补SQL基础的学生，也适合整天跟统计报表打交道的开发。

1. 先把题目数据模型吃透：spj并不是三个字母那么简单

1.1 表结构和业务含义

经典教材里，这个场景通常有供应商、零件、工程以及它们之间的供应关系，命名习惯是：

• s：供应商表，字段一般是sno, sname, status, city；
• p：零件表，字段一般是pno, pname, color, weight；
• j：工程表，字段一般是jno, jname, city；
• spj：供应关系表，字段一般是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，这一行会被过滤掉；如果集合里有正常值也有NULL，NULL部分不会匹配任何jno，但也不会导致整体查询报错。真正需要小心的是 NOT IN，一旦子查询结果里有NULL，NOT 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或者ROWNUM，SQL Server用OFFSET ... FETCH，不同数据库语法差异不小，迁移时要注意。

6. 性能复盘：索引、执行计划和慢查询

6.1 三种方案的执行计划对比

在我本地构建的测试数据里，spj大概30万行，j表1万行，p表几百行，我用MySQL 8.0分别跑了IN、EXISTS、GROUP BY三种写法，得到的结果集完全一致，但执行方式不同：

在我的测试环境里，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 BY+HAVING，能走索引一定走索引，最后用EXPLAIN收尾。按照这个套路来，不光这道题，单位里那些带“都必须满足”字眼的报表SQL，你也能一眼就看穿该怎么写。