# P47 47. 基础-多表查询-表子查询

> 本页是学习笔记，不等同于视频逐字稿。原声字幕未取得（匿名接口字幕轨为空且需登录，未使用任何账号），也没有逐句讲解记录；讲义只按节与页码对应，切点未逐句确证。

> 以同版讲义的公开镜像核对章节与页码（镜像非官方出品）；非逐字稿，示例为原创补充。

## P47 47. 基础-多表查询-表子查询

- 原视频：https://www.bilibili.com/video/BV1Kr4y1i7ru/?p=47（p=47），时长 7:05
- 讲义：5.6.5 表子查询 p84–85（公开镜像；按主题对应，切点 notexact）

- 本集练习数据：practice/p47.sql（先执行，再对照小例）

### 一句话理解

表子查询返回多行多列。整行匹配用 (列1,列2) IN (子查询)；放进 FROM 当派生表时，MySQL 8 要求必须起别名。

### 跟播：视频里 ↔ 本站

视频演示表子查询：整行 IN 匹配，以及把结果放进 FROM 当派生表。

| 视频里 / 原课字段 | 本站对应 |
|---|---|
| emp / dept | employee / department |
| 派生表必须起别名（如 e） | 同左（MySQL 8 强制要求） |

看完应能：能给派生表起别名，并说清派生表与 JOIN 的区别（一个当表、一个拼列）

看视频时以讲义表为准，跟练时用本站表；结构相同、数字不同。

### 步骤

1. 先看内层是不是多行多列。多行多列不能用 =，也不能按单列思维写，要把外层对应的几列用括号打包，整行去比。
2. 列的顺序和数量要对齐。(dept_id, salary) IN (SELECT dept_id, MAX(salary) ...) 左边两列，内层也必须两列、同顺序。
3. 放进 FROM 时它就是派生表。外层只能看到它 SELECT 出来的列，而且必须写 AS t；MySQL 8 不给别名报 ERROR 1248。
4. 能改写就改写，但先确认等价：表子查询换成 JOIN + GROUP BY 之前，先想清楚会不会因为一对多把行数放大、会不会丢掉 NULL 分组；改完用同一份数据对一遍行数和结果。想比快慢就看实际执行计划（EXPLAIN），别凭“JOIN 一定更快”下结论——数据量、索引和优化器都会变。

### SQL 示例

```
-- 表：department(1 研发部, 2 测试部, 3 运营部)
-- employee：小林22/研发8000/上级2，小周25/研发10000/无上级，小陈30/测试12000/上级2，小李21/无部门/NULL/上级2
-- 建表与插入语句见本页练习脚本 p47.sql（独立练习库 mysqlnotes_p47，跑一次就有这两张表）

-- 每行是“本部门最高工资”，整行匹配
SELECT id, name, dept_id, salary
FROM employee
WHERE (dept_id, salary) IN (
  SELECT dept_id, MAX(salary)
  FROM employee
  GROUP BY dept_id
);

-- 同一类结果放进 FROM，就是派生表（必须起别名）
SELECT t.dept_id, t.avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
) AS t
WHERE t.avg_salary > 9000;
```

说明：第一段内层给出两个组合：(1,10000)、(2,12000)，还有一组 (NULL,NULL) 因为小李没部门；IN 匹配不上 NULL，所以外层 2 行：小周、小陈。第二段派生表先算平均，研发部 9000、测试部 12000，外层筛大于 9000，只剩 2 号部门 1 行。 两段的共同点只是“内层都返回多行多列”：第一段拿每部门最高工资做整行匹配，第二段拿每部门平均工资当派生表再筛，聚合不同、行集也不同，不能互换；相同的是“多行多列可以当集合用 IN，也可以当表放 FROM”这个用法区别。先判断你要筛行，还是要再算一层。

### 易错点

- 多行多列的子查询用 = 接，直接报 1242 或列数错误。
- 派生表不给别名，MySQL 8 报 ERROR 1248 Every derived table must have its own alias。
- 内层 SELECT * 再让外层引用，列名重复且含义不清；派生表只留需要的列并起清晰别名。

### 30 秒速记

表子查询=多行多列；整行 IN 匹配；也能当 FROM 派生表；派生表必须起别名。

### 自测

1. 表子查询和列子查询的区别？
   - 答：列子查询一列多行，用 IN 比单值；表子查询多行多列，要按整行匹配或当派生表。
2. 派生表在 MySQL 8 不给别名会怎样？
   - 答：报 ERROR 1248，每张派生表都必须有别名。
3. 派生表和临时表是一回事吗？
   - 答：不是；派生表只在当前查询内存在，查询结束就没了，也不是真实建表。

### 材料与来源

- [平台视频页] BV1Kr4y1i7ru P47（平台标题） https://www.bilibili.com/video/BV1Kr4y1i7ru/?p=47
- [官方文档] MySQL 8.0 Reference Manual — Subqueries https://dev.mysql.com/doc/refman/8.0/en/subqueries.html

证据说明：本集无逐字稿（匿名 player/v2 返回 subtitles=[]、need_login_subtitle=true），并以讲义公开镜像讲义 5.6.5 表子查询（p84–85）核对章节与主题；画面静帧只用于确认场景，不是内容边界。示例表与数据为本笔记自建，不是课程原表；不含原始帧或字幕。 本课把多行多列子查询称表子查询，示例用整行 IN；FROM 派生表与别名要求为同主题补充说明。