SQL嵌套查询实战：从CPXS实验题到避坑改写

简介：面向数据库课程学习者与实验教学场景的《数据库实验5嵌套查询》实验报告文档，围绕SELECT统计查询、聚合函数、连接查询与嵌套查询四类核心技能展开，适合在完成基础SQL语法后集中训练复杂查询思路与实验报告撰写。压缩包内共1个doc文件，约642KB，内容以实验目的、实验内容、操作示例和实验思考组织，包含统计客户数量、库存总量、按客户分组计数、筛选上海客户、最高单价、LIKE前缀匹配、同城客户子查询、高于A01单价的产品以及未被订购产品等练习，读者可据此对照CPXS数据库逐步理解GROUP BY、HAVING、谓词与子查询的配合方式。已有931人学习，可作为课程实验参考、课后练习与查询语句自查材料。

1. 从一份“数据库实验5嵌套查询.doc”说起：它到底能解决什么

翻开这份文档，第一眼看到的是十几条针对同一套业务库的查询题：统计客户数、按城市分组计数、求 B 开头产品的最低价、找和某公司在同一城市的客户、找“没被订购过”的产品。题目不难，但每一道都精准踩在 SQL 学习者最容易含糊的地方——分组后过滤该写 WHERE 还是 HAVING、相关子查询和外层表怎么关联、!=接子查询为什么会翻车。这份实验文档的价值就在于它把统计查询、连接查询、嵌套查询三条线拧在同一套 CPXS 数据上反复练，而不是各讲各的。

它适合两类人：一是正在跟数据库实验课、需要交实验报告但 SQL 写不动的学生；二是工作里天天写 CRUD、一遇到“查没有下单的客户”“查高于均价的商品”就要翻旧代码的开发者。下面按“先把表结构立住、再逐类拆查询、最后避坑”的顺序走一遍，照着敲能复现，参数改动能套到自己库上。

2. 先把 CPXS 四张表立住：字段、关系与建库脚本

嵌套查询写不出来，八成不是语法问题，是没记住表和表之间靠谁连。这份实验从头到尾只用了四张表：客户表 CUSTOMER、产品表 PRODUCT、销售表 SALE，题目里还隐含一个“订购明细”的语义。先把结构固化成可执行的建库脚本，后面每条查询才有落点。

2.1 四张表的字段语义与关联键

从实验里的 SQL 反推，字段职责大致是这样：

关联键只有两个：CUSTOMER.CNO = SALE.CNO和PRODUCT.PCODE = SALE.PCODE。实验里第 11 题写的是FROM SALE,CUSTOMER WHERE CUSTOMER.CNO=SALE.CNO，这就是最朴素的隐式连接；第 13 题写的是FROM SALE,PRODUCT WHERE SALE.PCODE=PRODUCT.PCODE，同一个套路。把这两条等值条件记住，多表查询就不会写出笛卡尔积。

提示：原文档第 12 题的 SELECT 列表写成SELECT TELE CNAME，少了个逗号，实际应为SELECT TELE, CNAME。这类排版错误在 .doc 转出来的 SQL 里很常见，抄之前先扫一眼逗号。

2.2 建库建表与样例数据

不同数据库对自增、字符类型的要求不同，下面用通用性最好的写法，MySQL、openGauss、人大金仓这类都能直接跑，SQL Server 只需把VARCHAR换成NVARCHAR即可。

-- 客户表：CNO 为主键，SITE 用于按城市分组 CREATE TABLE CUSTOMER ( CNO VARCHAR(10) PRIMARY KEY, CNAME VARCHAR(50) NOT NULL, SITE VARCHAR(30), TELE VARCHAR(20) ); -- 产品表：STOCKS 库存量，PRICE 单价，后面的聚合查询都围绕它们 CREATE TABLE PRODUCT ( PCODE VARCHAR(10) PRIMARY KEY, PNAME VARCHAR(50) NOT NULL, PRICE DECIMAL(10,2), STOCKS INT ); -- 销售表：CNO+PCODE 联合主键，表示同一客户对同一产品只留一条汇总记录 CREATE TABLE SALE ( CNO VARCHAR(10), PCODE VARCHAR(10), OQUANTITY INT, PRIMARY KEY (CNO, PCODE), FOREIGN KEY (CNO) REFERENCES CUSTOMER(CNO), FOREIGN KEY (PCODE) REFERENCES PRODUCT(PCODE) ); -- 样例数据要覆盖实验里的边界：上海/非上海、B 开头产品、单价 10~20 之间、库存超 500 INSERT INTO CUSTOMER VALUES ('C01','美美','上海','021-1111'), ('C02','华信','上海','021-2222'), ('C03','北方商贸','北京','010-3333'), ('C04','西域商行','西安','029-4444'); INSERT INTO PRODUCT VALUES ('A01','打印机',1200.00,80), ('A02','硒鼓', 18.50, 600), ('B01','网线', 12.00, 900), ('B02','交换机',980.00,300), ('C01','鼠标', 15.00, 520); INSERT INTO SALE VALUES ('C01','A01',150), ('C01','A02',300), ('C02','A01',220), ('C02','B01',180), ('C03','A01',90);

联合主键(CNO, PCODE)是个关键设计：实验第 2 题GROUP BY CNO HAVING COUNT(PCODE)>=2求“订购两种以上产品”的客户，前提就是同一客户对同一产品只有一行。如果表里允许重复行，COUNT 数的是订单条数而不是产品种类数，结果直接失真。这一点在换到真实业务表时必须先确认，否则聚合口径全是错的。

数据插入顺序也有讲究：SALE 有外键指向另外两张表，必须先插 CUSTOMER 和 PRODUCT，否则会报外键约束错误。库存量和单价特意造了几条边界值——A02 库存 600、B01 库存 900 覆盖“库存超 500”；A02 单价 18.50、C01 单价 15.00 覆盖“10~20 之间”；B01、B02 覆盖LIKE 'B%'。样例数据不是随手编的，是照着后文的每道题反推出来的。

2.3 先验证关联是否成立

建完表别急着写复杂查询，先用一条两表连接看看数据能不能对上：

-- 验证 SALE 与 CUSTOMER、PRODUCT 的关联是否完整 SELECT s.CNO, c.CNAME, s.PCODE, p.PNAME, s.OQUANTITY FROM SALE s JOIN CUSTOMER c ON c.CNO = s.CNO JOIN PRODUCT p ON p.PCODE = s.PCODE ORDER BY s.CNO, s.PCODE;

这条语句跑出来应该有 5 行，和 SALE 的行数一致。如果行数变多，说明某张表主键不唯一，连接被放大了；如果变少，说明有孤儿记录（外键没生效）。我一般会把这个检查当成“后悔药”先跑一遍，后面嵌套查询结果可疑时，回到这一步比对原始明细，能立刻判断是子查询写错还是数据本身有问题。

3. 统计查询与分组过滤：COUNT/SUM/MAX 的落地写法

实验前半段（第 1 到第 9 题）全是聚合函数加分组，看似简单，但每道题都在考同一个判断：过滤条件该放在分组前还是分组后。

3.1 聚合函数与 GROUP BY 的执行顺序

理解顺序比背语法有用。一条带分组的查询，逻辑执行顺序是：FROM → WHERE → GROUP BY → 聚合函数 → HAVING → SELECT → ORDER BY。WHERE 在分组之前逐行过滤，所以它后面不能跟聚合函数；HAVING 在分组之后过滤，所以它可以直接用COUNT(*)、SUM(...)。实验第 6 题写的是：

-- 求库存量超过 500 的产品个数（按库存量分组后过滤） SELECT COUNT(PCODE) FROM PRODUCT GROUP BY STOCKS HAVING STOCKS > 500;

这条 SQL 能跑，但语义值得推敲：它先按库存量把产品分成若干组，再筛出库存量大于 500 的组，最后每组各返回一个计数。如果两个产品库存都恰好是 600，它们会被分到同一组，结果是 2 而不是两条 1。想表达“库存超 500 的产品有多少个”，更准确的写法是：

-- 更贴合题意的写法：先过滤再计数 SELECT COUNT(PCODE) AS over_500_cnt FROM PRODUCT WHERE STOCKS > 500;

WHERE STOCKS > 500是行级过滤，不涉及分组，语义就是“库存大于 500 的行有几条”。原文那种写法在结果上可能碰巧一致，但换个数据分布就会露馅。这类“能跑但语义偏”的语句，是实验文档里最值得拿来当反面教材的部分。

3.2 HAVING 的正确用法：按分组结果筛

真正的 HAVING 场景是第 2 题和第 8 题——条件本身就是聚合值。

-- 至少订购两种以上产品的客户编号和产品种类数 SELECT CNO, COUNT(PCODE) AS prod_kinds FROM SALE GROUP BY CNO HAVING COUNT(PCODE) >= 2;

这里的HAVING COUNT(PCODE) >= 2没法挪到 WHERE，因为 WHERE 执行时还没分组，拿不到“每组的种类数”。同理第 8 题：

-- 至少被两个以上客户订购、且每个客户订购数量超过 100 的产品 SELECT PCODE, SUM(OQUANTITY) AS total_qty FROM SALE WHERE OQUANTITY > 100 GROUP BY PCODE HAVING COUNT(PCODE) > 2;

这条的层次要看清：WHERE OQUANTITY > 100先剔掉小单，剩下的大单再按产品分组，HAVING COUNT(PCODE) > 2才筛“被两个以上客户订购”。原文这里写的是SELECT PCODE, OQUANTITY，而 OQUANTITY 既不在 GROUP BY 里也没被聚合，在开启ONLY_FULL_GROUP_BY的 MySQL 上会直接报错。改成SUM(OQUANTITY)才是稳定可移植的写法。

注意：MySQL 5.7 之后默认开启ONLY_FULL_GROUP_BY，SELECT 列表里出现的非聚合列必须出现在 GROUP BY 中。实验文档里有多条语句违反这条规则，比如第 7 题的SELECT PRICE, COUNT(PCODE) ... GROUP BY PRICE——这条其实是合规的，因为 PRICE 在 GROUP BY 里；但第 12 题、第 8 题的原始写法就未必。抄作业前先看自己库的sql_mode。

3.3 字符串匹配与区间统计

第 9 题和第 7 题是两类常用过滤的模板。LIKE 'B%'里的%匹配任意长度字符，性能上要注意：前缀匹配能用上索引，%B这种前后都带通配的用不上。区间统计优先用BETWEEN ... AND ...，它等价于>= AND <=，但可读性更好：

-- 以字母 B 开头的所有产品的最低单价 SELECT MIN(PRICE) AS min_price FROM PRODUCT WHERE PCODE LIKE 'B%'; -- 单价在 10~20 元之间产品的个数（过滤在分组前，用 WHERE 更直接） SELECT COUNT(PCODE) AS price_band_cnt FROM PRODUCT WHERE PRICE BETWEEN 10 AND 20;

第二条如果真要做成分组统计（比如按价格档看分布），才需要GROUP BY PRICE HAVING PRICE BETWEEN 10 AND 20，但那样返回的是“每个具体价格各有多少个产品”，不是“区间内总共有多少个”。题目问的是后者，所以 WHERE 版本才对。

3.4 多表连接统计的写法取舍

第 11 题开始把连接和统计混在一起：

-- 上海客户订购且数量大于 200 的客户编号、产品编号、联系人和数量 SELECT s.CNO, s.PCODE, c.CNAME, s.OQUANTITY FROM SALE s JOIN CUSTOMER c ON c.CNO = s.CNO WHERE c.SITE = '上海' AND s.OQUANTITY > 200;

原文档用的是逗号连接加 WHERE 等值条件，功能等价，但JOIN ... ON把连接条件和过滤条件分开写，读起来层次清楚，也不会漏掉连接条件导致笛卡尔积。工程里我一般统一用显式 JOIN，团队协作时少一类低级错误。这条语句返回的是明细行，不需要 GROUP BY；如果题目改成“上海客户订购总量”，才需要加SUM(OQUANTITY)和GROUP BY s.CNO。

三个统计函数的适用边界也顺带说清：COUNT(*) 数行数，COUNT(列) 跳过该列为 NULL 的行，两者在有空值时结果不同；SUM 和 AVG 忽略 NULL，如果整组全为 NULL 则返回 NULL 而非 0，报表里做除法时要留意；MAX/MIN 对字符串是按排序规则比较，对日期是取最晚/最早，别想当然。

4. 嵌套查询三类写法：标量子查询、IN 与 NOT EXISTS

实验后半段（第 12 到第 14 题）是嵌套查询的主场。三道的难度是递进的：等值比较的标量子查询、多值比较的 IN、以及取反的“没有被订购”。

4.1 标量子查询：返回单值的子查询

第 12 题是最好懂的一种：

-- 查询与"美美"公司在同一城市的客户公司名称及联系电话 SELECT CNAME, TELE FROM CUSTOMER WHERE SITE = (SELECT SITE FROM CUSTOMER WHERE CNAME = '美美');

子查询(SELECT SITE FROM CUSTOMER WHERE CNAME = '美美')必须只返回一个值，否则外层=会报“子查询返回多于一行”。这里能成立是因为 CNAME 在业务上唯一；如果表里存在两家同名的“美美”，这条 SQL 立刻翻车。稳妥做法是加LIMIT 1或者改用 IN：

-- 防御写法：同名公司不唯一时也不会报错，只是结果可能变多 SELECT CNAME, TELE FROM CUSTOMER WHERE SITE IN (SELECT SITE FROM CUSTOMER WHERE CNAME = '美美');

另外别忘了结果里会包含“美美”自己，如果要排除，加AND CNAME <> '美美'。实验文档没提这茬，但真实报表里“找出同城同行”通常是要排除自己的。

第 13 题是标量子查询和连接的组合：

-- 订购了单价比 A01 高的产品的客户编号、产品编号和数量 SELECT s.PCODE, s.CNO, s.OQUANTITY FROM SALE s JOIN PRODUCT p ON p.PCODE = s.PCODE WHERE p.PRICE > (SELECT PRICE FROM PRODUCT WHERE PCODE = 'A01');

子查询先算出 A01 的单价 1200.00，外层再筛出单价高于它的销售明细。注意外层必须连 PRODUCT 表，因为单价在 PRODUCT 上、订购量在 SALE 上。原文档写成FROM SALE,PRODUCT WHERE SALE.PCODE=PRODUCT.PCODE AND PRICE>(...)，注意外层PRICE没加表前缀——只要 PRODUCT 是唯一带 PRICE 列的表就不报错，但一旦子查询里也涉及 PRICE，解析器可能产生歧义。加表别名是最省心的习惯。

4.2 IN 与 NOT IN：多值比较和它的 NULL 陷阱

第 14 题表面简单，坑最深：

-- 查询没有被订购的产品的编号和名称（原文写法，有隐患） SELECT PCODE, PNAME FROM PRODUCT WHERE PCODE != (SELECT PCODE FROM SALE);

这条 SQL 在 SALE 有多行时必然报错：!=是标量比较，右侧子查询返回多行直接失败。实验文档里这条大概率跑不通，属于典型的教学材料笔误。正确的两种写法：

-- 写法一：NOT IN，语义直观 SELECT PCODE, PNAME FROM PRODUCT WHERE PCODE NOT IN (SELECT PCODE FROM SALE); -- 写法二：NOT EXISTS，空值安全，大表上通常也更稳 SELECT p.PCODE, p.PNAME FROM PRODUCT p WHERE NOT EXISTS ( SELECT 1 FROM SALE s WHERE s.PCODE = p.PCODE );

NOT IN 的致命陷阱是 NULL：如果子查询结果里含 NULL，x NOT IN (..., NULL)的三值逻辑会让整个条件既不真也不假，最终一行都不返回。SALE.PCODE 因为有外键和非空约束一般不会有 NULL，但只要换成一张可能为空的表，这就是血泪坑。NOT EXISTS 不受影响，因为它判断的是“关联行存在与否”，NULL 不参与比较。我现在的习惯是：子查询列可能为空时一律用 NOT EXISTS。

4.3 相关子查询：子查询引用外层列

相关子查询是嵌套查询里最需要建立直觉的一类：子查询不是先算一次，而是外层每行都带着自己的值进去算一次。第 13 题其实已经隐含这种结构，更典型的场景是“查出每个客户订购量高于该客户平均订购量的记录”：

-- 相关子查询：外层每一行都把 c.CNO 传给子查询 SELECT s.CNO, s.PCODE, s.OQUANTITY FROM SALE s WHERE s.OQUANTITY > ( SELECT AVG(s2.OQUANTITY) FROM SALE s2 WHERE s2.CNO = s.CNO -- 这一句把内外层绑定起来 );

WHERE s2.CNO = s.CNO就是“相关条件”，去掉它子查询就退化成全局平均，语义完全变了。相关子查询在逻辑上清晰，但执行时可能对外层每行都跑一遍子查询，数据量大时要看执行计划；常见做法是改写成 JOIN 加派生表，或者在支持的库上用窗口函数AVG(...) OVER (PARTITION BY CNO)一次算完。

4.4 三值逻辑与 EXISTS 的取舍

SQL 的逻辑不是二值，是 TRUE / FALSE / UNKNOWN 三值。NULL = NULL结果是 UNKNOWN，WHERE只放行 TRUE，所以NOT IN遇 NULL 全灭，而NOT EXISTS只关心“有没有行”。这个差异在实验第 14 题上体现得最典型：用!=写法是语法/语义双重错误，用NOT IN能跑但怕 NULL，用NOT EXISTS最稳。

改写成 EXISTS 时最常见的错误是漏写关联条件，变成SELECT 1 FROM SALE这种与外表无关的子查询——只要 SALE 非空，结果就恒真，整条查询退化成全表扫描。写完先拿小数据集对一遍行数，能省不少排查时间。

5. 避坑与排查：这份实验里最容易翻车的五处

实验文档本身有笔误，不同数据库行为又有差异，照抄跑不通是常态。下面五条是被问得最多的，按“现象 → 原因 → 解决”写。

5.1 第 14 题报“子查询返回多于一行”

现象：执行WHERE PCODE != (SELECT PCODE FROM SALE)直接报错，提示子查询返回多行。

原因：!=后面只能接标量，而 SALE 里每个产品可能对应多条销售记录，子查询天然返回多行。这是实验文档的写法问题，不是环境问题。

解决：改用NOT IN或NOT EXISTS，如 4.2 节所示。若坚持用比较运算符，必须给子查询加聚合或LIMIT 1，但语义就不是“所有未订购产品”了。

5.2 GROUP BY 报“不是 GROUP BY 表达式”

现象：SELECT CNO, COUNT(PCODE) FROM SALE GROUP BY CNO能跑，但把 COUNT 换成 OQUANTITY 就报错。

原因：MySQL 5.7+ 默认ONLY_FULL_GROUP_BY，SELECT 里的非聚合列必须出现在 GROUP BY 中。实验第 8 题原始写法正好踩线。

解决：把非聚合列改成聚合（如SUM(OQUANTITY)），或把它加进 GROUP BY。不建议为了跑通去关sql_mode，那只是把错误藏起来。查当前设置：SELECT @@sql_mode;。

5.3 中文条件查不出数据

现象：WHERE SITE = '上海'明明表里有“上海”，结果为空或乱码。

原因：客户端、连接、表三处字符集不一致。常见是表建成了 latin1，或用\G输出时客户端编码不对。

解决：建库时统一CHARACTER SET utf8mb4，连接串显式指定字符集；排查用SHOW VARIABLES LIKE 'character_set%';对比服务端和客户端。SQL Server 里则注意NVARCHAR加N'上海'前缀。

5.4 连接后行数变多

现象：两表连接查出来的记录比 SALE 表行数还多，聚合结果跟着虚高。

原因：连接键不唯一，比如 CUSTOMER 中 CNO 有重复，或 SALE 中同一客户同一产品有多条记录，JOIN 时一对多被放大。

解决：先跑SELECT CNO, COUNT(*) FROM CUSTOMER GROUP BY CNO HAVING COUNT(*)>1;找出重复键；确认业务上是否真需要去重。聚合前先看清基数，是避免统计失真的第一步。

5.5 子查询能跑但结果对不上

现象：单独跑子查询有结果，套进外层查询却是空集。

原因：相关子查询的关联条件写错（内外层列绑错表），或者NOT IN子查询列里有 NULL，整条条件变 UNKNOWN。

解决：把子查询单独拎出来看返回值，特别是有没有 NULL；相关子查询把关联条件注释掉对比结果差异，能快速定位是绑定问题还是数据问题。这一步花两分钟，比盯着外层 SQL 猜半小时有效。

6. 从实验题到生产查询：改写与验证的两个习惯

这份文档练的是查询本身，但真正把 SQL 写稳，靠的是两条习惯：先验证基数，再做等价改写。

第一条是验证基数。任何带 JOIN 或子查询的语句，动手前先确认参与连接的键唯一性。我的固定动作是三条检查：SELECT COUNT(*) FROM 主表;、对连接键跑GROUP BY ... HAVING COUNT(*)>1、把连接结果和主表行数对比。实验里第 11 题那种“SALE 连 CUSTOMER 再连 PRODUCT”的查询，只要 CNO 和 PCODE 都是主键，行数就恒等于 SALE 行数，结果可信；一旦换成真实业务表，这两条唯一性往往不成立，不先查就会得到看似合理实则翻倍的统计结果。

第二条是等价改写。同一道题至少有三种写法，性能差异可能很大。以“没被订购的产品”为例：

-- 写法 A：NOT IN，遇 NULL 会静默返回空集 SELECT PCODE, PNAME FROM PRODUCT WHERE PCODE NOT IN (SELECT PCODE FROM SALE); -- 写法 B：NOT EXISTS，空值安全 SELECT p.PCODE, p.PNAME FROM PRODUCT p WHERE NOT EXISTS (SELECT 1 FROM SALE s WHERE s.PCODE = p.PCODE); -- 写法 C：LEFT JOIN + IS NULL，某些优化器下更快 SELECT p.PCODE, p.PNAME FROM PRODUCT p LEFT JOIN SALE s ON s.PCODE = p.PCODE WHERE s.PCODE IS NULL; -- 三者在同一份数据上应该返回完全相同的行，先对行数再谈性能

三种写法在当前样例数据上都应返回 1 行（C01 鼠标，未被任何客户订购）。改写的验证方法是：先在小数据集上对齐结果行数和内容，再拿EXPLAIN看执行计划。MySQL 里NOT EXISTS和LEFT JOIN ... IS NULL常被优化成同类计划，而NOT IN在子查询列可空时不仅结果错，还可能走不上索引。

再说一个实验文档没覆盖但生产里必踩的点：嵌套层数。三层以上的嵌套查询可读性急剧下降，维护成本高。我的经验法则是超过两层就把内层抽成 CTE（WITH ... AS）或者临时表，一是好调试（每层单独跑），二是优化器有时能对 CTE 做更好的物化决策。openGauss、PostgreSQL、SQL Server 都支持 CTE，MySQL 8.0 也支持，写起来比嵌套清爽得多：

-- 用 CTE 把"订购量高于客户平均"的逻辑拆开，便于逐层验证 WITH cust_avg AS ( SELECT CNO, AVG(OQUANTITY) AS avg_qty FROM SALE GROUP BY CNO ) SELECT s.CNO, s.PCODE, s.OQUANTITY FROM SALE s JOIN cust_avg a ON a.CNO = s.CNO WHERE s.OQUANTITY > a.avg_qty;

这条和 4.3 节的相关子查询版本结果一致，但拆成两步后，cust_avg可以单独跑出来核对每个客户的平均值，出问题一眼能看出是哪一层。

从那以后我每次拿到一份实验文档或旧 SQL，都会先把建表脚本和数据跑一遍、确认基数，再把嵌套改成可分层验证的写法，最后才谈优化。这套动作看着笨，但能挡住九成的“结果看着对其实错”的坑。这份嵌套查询实验的价值也正在这里：题目小、边界清，正好用来把这套习惯练成肌肉记忆。希望帮到你。

本文还有配套的精品资源，点击获取