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

一句话理解

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

按这 4 步做

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 再连一次，变成三表。

小例

先准备本集练习数据：p49.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 是本页原创的 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;

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;

易错点

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

30 秒速记

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

材料与来源