第九单元 · 子查询与综合练习 · 第 20 / 21 节

子查询（二）：一行多列、一整张表

换一套 lesson20_dept（3 行）和 lesson20_emp（7 行，白露与秦川同薪同上级、苏航无上级、江遥无部门），用“同薪同上级”讲行子查询：括号整行比较、NULL 让整行失效（1242 / 1241）；再用“每部门最高工资”和“部门平均工资”讲表子查询的整行 IN 与 FROM 派生表（并列保留、NULL 分组不匹配、1248 必须起别名）。

2 个步骤 · 进度只存本机

约 22 分钟。上一节的内层要么返回一个值，要么返回一列。这一节看另外两种：一行多列（几个字段必须来自同一个人）和多行多列（一张小表，可以当集合，也可以当临时表）。难点仍然是先看清形状。

SSH / Linux 终端MySQL 终端输出示例（只读）复制按钮只复制命令，不含 $ / mysql> 提示符和输出。

已完成 0 / 2 步

你现在在哪个提示符

应该在 mysql>。断了就先回到学习终端（PowerShell）：

SSH / Linux 终端bash

ssh root@8.148.204.152

SSH / Linux 终端bash

mysql-learning

进去以后确认当前库：

MySQL 终端sql

SELECT DATABASE();

输出示例（只读，不要粘贴执行）text不可复制

+------------+
| DATABASE() |
+------------+
| itcast     |
+------------+
1 row in set (0.00 sec)

显示 itcast 就对了。如果是 NULL，执行 USE itcast;。

第一步：行子查询——两个条件必须来自同一行

这一步做完了

本节换一套自己的表，前缀 lesson20_，和上一节没有关系：

MySQL 终端sql

SHOW TABLES LIKE 'lesson20_%';

输出示例（只读，不要粘贴执行）text不可复制

Empty set (0.00 sec)

Empty set 才往下建；已经有表就跳过建表和插入，直接看本节末尾的终态核对，不要删表。

MySQL 终端sql

CREATE TABLE lesson20_dept (
  id   INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  CONSTRAINT uk_lesson20_dept_name UNIQUE (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 0 rows affected (0.04 sec)

MySQL 终端sql

INSERT INTO lesson20_dept (id, name) VALUES (1, '品牌部'), (2, '渠道部'), (3, '仓储部');

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MySQL 终端sql

CREATE TABLE lesson20_emp (
  id         INT PRIMARY KEY,
  name       VARCHAR(20) NOT NULL,
  age        TINYINT UNSIGNED,
  dept_id    INT,
  salary     INT,
  manager_id INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 0 rows affected (0.02 sec)

MySQL 终端sql

INSERT INTO lesson20_emp (id, name, age, dept_id, salary, manager_id) VALUES (1, '苏航', 28, 1, 9000, NULL), (2, '林澈', 32, 1, 9000, 1), (3, '白露', 30, 2, 11000, 1), (4, '秦川', 35, 2, 11000, 1), (5, '顾言', 24, 3, 6800, 3), (6, '江遥', 29, NULL, 8200, 3), (7, '温宁', 41, 3, 11000, 2);

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

苏航是老板，没有上级；江遥没有部门。白露和秦川的“工资 + 上级”完全相同，这是下一步要用的重复行。

问题：和白露工资、直属上级都相同的人是谁？ 这两个条件必须来自同一个人。先看白露自己的这一行：

MySQL 终端sql

SELECT salary, manager_id FROM lesson20_emp WHERE id = 3;

输出示例（只读，不要粘贴执行）text不可复制

+--------+------------+
| salary | manager_id |
+--------+------------+
|  11000 |          1 |
+--------+------------+
1 row in set (0.00 sec)

一行两列：11000 和 1。把这两列用括号打包，整体去比：

MySQL 终端sql

SELECT id, name, salary, manager_id FROM lesson20_emp WHERE (salary, manager_id) = (SELECT salary, manager_id FROM lesson20_emp WHERE id = 3) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+----+--------+--------+------------+
| id | name   | salary | manager_id |
+----+--------+--------+------------+
|  3 | 白露   |  11000 |          1 |
|  4 | 秦川   |  11000 |          1 |
+----+--------+--------+------------+
2 rows in set (0.00 sec)

两行：白露和秦川。行子查询的语义是“两个值同时相等”，不是“任意一个相等”。

为什么不能拆成两个独立子查询？看一条真实的反例：工资取全公司最高，上级却取江遥的上级：

MySQL 终端sql

SELECT name FROM lesson20_emp WHERE salary = (SELECT MAX(salary) FROM lesson20_emp) AND manager_id = (SELECT manager_id FROM lesson20_emp WHERE id = 6);

输出示例（只读，不要粘贴执行）text不可复制

Empty set (0.00 sec)

最高工资 11000 有白露、秦川、温宁三人，但江遥的上级是 3 号白露；没有谁同时满足“工资 11000、上级 3”。两个子查询各取各的值，拼出来的组合可能根本不存在。行子查询保证两个值来自同一行，就是为了避免这种错配。

行比较遇到 NULL 会整体失效。拿苏航的 (9000, NULL) 去比：

MySQL 终端sql

SELECT name FROM lesson20_emp WHERE (salary, manager_id) = (SELECT salary, manager_id FROM lesson20_emp WHERE id = 1) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

Empty set (0.00 sec)

一行也没有，连苏航自己都匹配不上。因为 NULL = NULL 的结果是 unknown，整行比较就不为真。要匹配“上级为空”，得单独写 manager_id IS NULL，或者用 <=>（NULL 安全等于）；本课记住前者就够。

行子查询也只接受一行。内层多行时同样报 1242：

MySQL 终端sql

SELECT name FROM lesson20_emp WHERE (salary, manager_id) = (SELECT salary, manager_id FROM lesson20_emp WHERE salary = 11000);

输出示例（只读，不要粘贴执行）text不可复制

ERROR 1242 (21000): Subquery returns more than 1 row

括号里的列数也必须对得上。左边两列、右边只给一列，会报 1241：

MySQL 终端sql

SELECT name FROM lesson20_emp WHERE (salary, manager_id) = (SELECT salary FROM lesson20_emp WHERE id = 3);

输出示例（只读，不要粘贴执行）text不可复制

ERROR 1241 (21000): Operand should contain 2 column(s)

1242 是“行太多”，1241 是“列数不对”，两个数字别混。

第二步：表子查询——多行多列，可以当集合也可以当表

这一步做完了

表子查询返回多行多列。它有两种用法：整行 IN 当集合用，或者放进 FROM 当一张临时表（派生表）。

先看“每个部门的最高工资”：

MySQL 终端sql

SELECT dept_id, MAX(salary) AS 最高工资 FROM lesson20_emp GROUP BY dept_id;

输出示例（只读，不要粘贴执行）text不可复制

+---------+--------------+
| dept_id | 最高工资     |
+---------+--------------+
|       1 |         9000 |
|       2 |        11000 |
|       3 |        11000 |
|    NULL |         8200 |
+---------+--------------+
4 rows in set (0.00 sec)

四行：品牌部 9000、渠道部 11000、仓储部 11000，还有一行 NULL, 8200——江遥没有部门，GROUP BY 会把他单独分成一组。这一行待会儿匹配不上，正好用来说明 NULL 不参与整行相等。

把这张两列的表整行塞进 IN：

MySQL 终端sql

SELECT id, name, dept_id, salary FROM lesson20_emp WHERE (dept_id, salary) IN (SELECT dept_id, MAX(salary) FROM lesson20_emp GROUP BY dept_id) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+----+--------+---------+--------+
| id | name   | dept_id | salary |
+----+--------+---------+--------+
|  1 | 苏航   |       1 |   9000 |
|  2 | 林澈   |       1 |   9000 |
|  3 | 白露   |       2 |  11000 |
|  4 | 秦川   |       2 |  11000 |
|  7 | 温宁   |       3 |  11000 |
+----+--------+---------+--------+
5 rows in set (0.00 sec)

五行。品牌部两人都是 9000，两条都留下；渠道部白露、秦川都是 11000，也两条都留下；仓储部只有温宁 11000 入选。江遥的 (NULL, 8200) 不在结果里，因为 NULL 参与整行比较得到 unknown。

注意“最高工资”有并列时，IN 会把并列的人全部返回；这不是重复，是并列第一。要只留一个人，得再加规则（比如 id 最小），那是另一道题。

第二种用法：把子查询放进 FROM。它这时叫派生表，可以先算好每部门平均，再在外层筛：

MySQL 终端sql

SELECT t.dept_id, t.avg_salary FROM (SELECT dept_id, AVG(salary) AS avg_salary FROM lesson20_emp WHERE dept_id IS NOT NULL GROUP BY dept_id) AS t WHERE t.avg_salary >= 9000 ORDER BY t.dept_id;

输出示例（只读，不要粘贴执行）text不可复制

+---------+------------+
| dept_id | avg_salary |
+---------+------------+
|       1 |  9000.0000 |
|       2 | 11000.0000 |
+---------+------------+
2 rows in set (0.00 sec)

品牌部 9000.0000、渠道部 11000.0000。>= 9000 把正好等于 9000 的品牌部也留下了；仓储部 8900 被筛掉。派生表只在当前查询里存在，查询结束就没了，不是真的建了一张表。

派生表必须起别名。MySQL 8 里省掉 AS t 会直接报 1248：

MySQL 终端sql

SELECT dept_id FROM (SELECT dept_id, AVG(salary) AS avg_salary FROM lesson20_emp GROUP BY dept_id);

输出示例（只读，不要粘贴执行）text不可复制

ERROR 1248 (42000): Every derived table must have its own alias

别名不只是为了消错：外层只能用 t.列名 引用派生表 SELECT 出来的列，原表的其他列它看不见。

常见报错怎么办

报错	什么意思	怎么办
`1242 Subquery returns more than 1 row`	内层多行，外层却用 `=` 接	改成 `IN`，或让内层按主键/聚合只返回一行
`1241 Operand should contain N column(s)`	括号两边列数不一致	数一数左右各几列，顺序也要对齐
`1248 Every derived table must have its own alias`	`FROM` 后面的子查询没起别名	补 `AS t`，外层用 `t.列名`
`1062 Duplicate entry`	种子数据重复插入	表已存在就跳过建表和插入，用行数核对，不要删表

说明

MySQL 终端sql

SELECT naem FROM lesson20_emp;

输出示例（只读，不要粘贴执行）text不可复制

ERROR 1054 (42S22): Unknown column 'naem' in 'field list'

1054 只说明“找不到这个列”，不说明表坏了。先 DESC lesson20_emp; 看真实列名，再改查询。中文别名写错也会落到这一类。

说明

MySQL 终端sql

INSERT INTO lesson20_dept (id, name) VALUES (1, '品牌部');

输出示例（只读，不要粘贴执行）text不可复制

ERROR 1062 (23000): Duplicate entry '1' for key 'lesson20_dept.PRIMARY'

表已经存在时重跑插入就会撞主键。跳过建表和插入、按行数核对即可，不要为了“重来”去删表。

练习

1. 查出与 3 号员工工资、直属上级都相同的员工。

说明

内层按主键 id = 3 只会返回一行两列；外层用括号把 salary, manager_id 打包，列序与内层一致。

说明

MySQL 终端sql

SELECT id, name, salary, manager_id FROM lesson20_emp WHERE (salary, manager_id) = (SELECT salary, manager_id FROM lesson20_emp WHERE id = 3) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+----+--------+--------+------------+
| id | name   | salary | manager_id |
+----+--------+--------+------------+
|  3 | 白露   |  11000 |          1 |
|  4 | 秦川   |  11000 |          1 |
+----+--------+--------+------------+
2 rows in set (0.00 sec)

说明

拆成 salary = (...) AND manager_id = (...) 两个独立子查询，两个值可能来自不同的人；本例恰好都来自 3 号，但换成“最高工资 + 江遥的上级”就一行也查不到。

2. 查出每个部门工资最高的员工。

说明

先按部门分组求 MAX(salary)，得到“部门号 + 最高工资”两列；外层把这两列用括号打包，整行 IN。

说明

MySQL 终端sql

SELECT id, name, dept_id, salary FROM lesson20_emp WHERE (dept_id, salary) IN (SELECT dept_id, MAX(salary) FROM lesson20_emp GROUP BY dept_id) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+----+--------+---------+--------+
| id | name   | dept_id | salary |
+----+--------+---------+--------+
|  1 | 苏航   |       1 |   9000 |
|  2 | 林澈   |       1 |   9000 |
|  3 | 白露   |       2 |  11000 |
|  4 | 秦川   |       2 |  11000 |
|  7 | 温宁   |       3 |  11000 |
+----+--------+---------+--------+
5 rows in set (0.00 sec)

说明

用 = 接多行报 1242；或者以为并列最高只该留一个人——IN 会把同薪同部门的都返回。江遥没有部门，不会被 NULL 分组匹配到。

3. 查出平均工资高于 9500 的部门。

说明

先把“部门号 + 平均工资”做成派生表，起别名，再在外层 WHERE 里筛平均工资。别忘了排除没有部门的人。

说明

MySQL 终端sql

SELECT t.dept_id, t.avg_salary FROM (SELECT dept_id, AVG(salary) AS avg_salary FROM lesson20_emp WHERE dept_id IS NOT NULL GROUP BY dept_id) AS t WHERE t.avg_salary > 9500 ORDER BY t.dept_id;

输出示例（只读，不要粘贴执行）text不可复制

+---------+------------+
| dept_id | avg_salary |
+---------+------------+
|       2 | 11000.0000 |
+---------+------------+
1 row in set (0.00 sec)

说明

省掉 AS t 报 1248；把 WHERE AVG(salary) > 9500 写进子查询里，聚合不能直接放在 WHERE 中。条件要放在外层，或者用 HAVING，但本课练的是派生表。

4. 查与苏航工资、直属上级都相同的员工。结果为什么是空的？

说明

先单独跑内层，看苏航的 manager_id 是什么。NULL 参与 = 比较会得到什么？

说明

MySQL 终端sql

SELECT name FROM lesson20_emp WHERE (salary, manager_id) = (SELECT salary, manager_id FROM lesson20_emp WHERE id = 1) ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

Empty set (0.00 sec)

说明

以为至少应查出苏航自己。(9000, NULL) = (9000, NULL) 里有一对 NULL，结果是 unknown，不是 true。要查“没有上级的人”，写 WHERE manager_id IS NULL。

本单元验收

能说出行子查询与“两个独立标量子查询”的区别，并举出一个会拼错人的例子。
能解释行比较里出现 NULL 为什么整行都不相等。
能写出整行 IN 的表子查询，并知道并列值会全部保留。
能把聚合结果放进 FROM 当派生表，并给别名。

做到这里

收工前核对本节留下的数据：

MySQL 终端sql

SELECT (SELECT COUNT(*) FROM lesson20_dept) AS 部门数, (SELECT COUNT(*) FROM lesson20_emp) AS 员工数, (SELECT COUNT(*) FROM lesson20_emp WHERE dept_id IS NULL) AS 无部门, (SELECT COUNT(*) FROM lesson20_emp WHERE manager_id IS NULL) AS 无上级, (SELECT COUNT(DISTINCT dept_id) FROM lesson20_emp WHERE dept_id IS NOT NULL) AS 有员工部门;

输出示例（只读，不要粘贴执行）text不可复制

+-----------+-----------+-----------+-----------+-----------------+
| 部门数    | 员工数    | 无部门    | 无上级    | 有员工部门      |
+-----------+-----------+-----------+-----------+-----------------+
|         3 |         7 |         1 |         1 |               3 |
+-----------+-----------+-----------+-----------+-----------------+
1 row in set (0.00 sec)

本节停在这里：四类子查询的形状已经走完，下一节把它们和连接、分组、聚合混在一起，做讲义 5.7 的十二道综合题。那十二题会换一套 lesson21_* 表，不读本节的数据。

来源与边界

本节对应 BV1Kr4y1i7ru 平台 part 46「46. 基础-多表查询-行子查询」、part 47「47. 基础-多表查询-表子查询」，主题与同版讲义《MySQL-基础篇》5.6.4–5.6.5（p83–85）对应；讲义为公开镜像，仅作事实核对。无逐字稿，未声称听过讲解；来源只写平台 part 号与讲义页码。本节表名、数据、查询与练习均为本教程原创，未复制讲义正文；讲义行子查询用“张无忌”的同薪同上级，本节改用白露，数据另行设计。课程停在 P50，本节不涉及事务、索引、引擎或执行计划。