# P49 49. 基础-多表查询-练习2

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

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

## P49 49. 基础-多表查询-练习2

- 原视频：https://www.bilibili.com/video/BV1Kr4y1i7ru/?p=49（p=49），时长 21:29
- 讲义：5.7 多表查询案例（第 5–12 题所在段） p86–88（公开镜像；按主题对应，切点 notexact）

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

### 一句话理解

先把需求拆成单句验证：分组统计、相关子查询各一条；薪资档位用 salgrade 的区间连接，再把部门连上就是三表。

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

视频停在「所有员工的工资等级」与「研发部员工及工资等级」两题上，第二题就是三表。

| 视频里 / 原课字段 | 本站对应 |
|---|---|
| 讲义 emp 17 行 × dept 6 行 × salgrade 8 档 | 本站 employee 4 行 × department 3 行 × salgrade 4 档 |
| emp.salary BETWEEN salgrade.losal AND salgrade.hisal | employee.salary BETWEEN salgrade.losal AND salgrade.hisal |

看完应能：能先写出「工资落哪档」，再加一条部门连接变成三表

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

### 步骤

1. 统计类先定「以谁为基准」。要每个部门都出现（包括没人），就以 department 为左表 LEFT JOIN employee，别反过来。
2. 计数时用 COUNT(e.id) 而不是 COUNT(*)。左连接会给没人部门补一行 NULL，COUNT(*) 会把它算成 1 人，COUNT(e.id) 才是 0。
3. 相关子查询里外层的值会传进内层。e.salary > (SELECT AVG(x.salary) ... WHERE x.dept_id = e.dept_id) 读作「高于本部门平均」，内层每行重算。
4. 查薪资档次不用等值连接：`e.salary BETWEEN s.losal AND s.hisal` 就是一条非等值连接；要部门名就把 department 再连一次，变成三表。

### SQL 示例

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

-- 每部门人数与平均工资（运营部也要出现）
SELECT d.name AS 部门, COUNT(e.id) AS 人数, AVG(e.salary) AS 平均工资
FROM department d
LEFT JOIN employee e ON e.dept_id = d.id
GROUP BY d.id, d.name
ORDER BY d.id;

-- 高于本部门平均工资的人（相关子查询）
SELECT e.name AS 员工, e.dept_id AS 部门号, e.salary AS 工资
FROM employee e
WHERE e.salary > (
  SELECT AVG(x.salary) FROM employee x WHERE x.dept_id = e.dept_id
);
```

说明：第一段 3 行：研发部 2 人 / 9000、测试部 1 人 / 12000、运营部 0 人 / NULL。第二段研发部平均 9000，小周 10000 入选；测试部只有小陈，12000 不大于 12000；小李没有部门，内层算不出平均（NULL），不入选。共 1 行。 反连接写法：SELECT d.id, d.name FROM department d LEFT JOIN employee e ON e.dept_id = d.id WHERE e.id IS NULL，结果只有 3 运营部。这三个数字就是核对后面所有统计题的基准，先记住它们。

### 第一步：工资落哪一档（salgrade 区间连接，两张表）

```sql
-- salgrade 是本页原创的 4 档小表（grade, losal, hisal），不是讲义那 8 档
SELECT e.name AS 员工, e.salary AS 工资, s.grade AS 档位
FROM employee e
JOIN salgrade s ON e.salary BETWEEN s.losal AND s.hisal
ORDER BY e.id;
```

说明：3 行：小林 8000→2 档、小周 10000→3 档、小陈 12000→3 档。BETWEEN 含两端，所以 12000 落在 9001–12000 的第 3 档。小李的 salary 是 NULL，NULL BETWEEN … 得 unknown，连不上任何档，这一行就消失了——非等值连接只保留「真的落进某个区间」的行。

预期结果：小林 8000 2｜小周 10000 3｜小陈 12000 3（小李 NULL 不出现）

### 第二步：再看是哪个部门（三表）

```sql
SELECT e.name AS 员工, d.name AS 部门, s.grade AS 档位
FROM employee e
JOIN department d ON e.dept_id = d.id
JOIN salgrade s ON e.salary BETWEEN s.losal AND s.hisal
ORDER BY e.id;
```

说明：3 行：小林 研发部 2、小周 研发部 3、小陈 测试部 3。两条连接条件互相独立：一条是等值（dept_id = id），一条是区间（BETWEEN losal AND hisal）。三表不是「一条 ON 写三个表」，而是每连一张表就补一条 ON，顺序按「先等值、后区间」读最清楚。

预期结果：小林 研发部 2｜小周 研发部 3｜小陈 测试部 3

### 易错点

- LEFT JOIN 后用 COUNT(*) 统计人数，会把「没人」的部门算成 1。
- 相关子查询忘写 x.dept_id = e.dept_id，就退化成全公司平均，结果看似合理但是错的。
- 区间连接漏了端点（写成 salary > losal AND salary < hisal），正好等于档位边界的工资会掉档。
- 以为 NULL 工资会落进第 1 档：NULL 参与 BETWEEN 得 unknown，不会匹配任何档。

### 30 秒速记

统计以保留侧为左表；COUNT(列) 才排除 NULL；相关子查询靠内外层字段挂钩；反连接用 LEFT JOIN + IS NULL。

### 自测

1. 运营部为什么会出现在结果里，人数却是 0？
   - 答：department 是左表，LEFT JOIN 保留全部部门；COUNT(e.id) 不数补出来的 NULL 行。
2. 相关子查询漏写 dept_id = e.dept_id 会怎样？
   - 答：变成和全公司平均比，结果错但通常不报错。
3. 统计每个部门人数为什么不用 COUNT(*)？
   - 答：LEFT JOIN 补出的 NULL 行也会被 COUNT(*) 计入，没人部门会变成 1。

### 材料与来源

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

证据说明：本集无逐字稿（匿名 player/v2 返回 subtitles=[]、need_login_subtitle=true），依据平台标题、讲义 p86「5.7 多表查询案例」第 5–6 题与公开静帧核对：原课用 emp / dept / salgrade 三表，salgrade 是 grade, losal, hisal 三列共 8 档。本站用 employee / department 加一张 4 档原创 salgrade 复现同一形状，表名、行数与档位数字都不同，不能拿本站结果对讲义答案。