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

一句话理解

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

按这 4 步做

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 一定更快”下结论——数据量、索引和优化器都会变。

小例

先准备本集练习数据：p47.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 秒速记

自测（3 题，先答再展开）

材料与来源