阿里巴巴大数据面试SQL-三表连接查询部门薪资统计

⚠️ 待修正

一、题目背景

这道题来自阿里巴巴HR数据平台的BI开发岗面试。阿里每年要进行薪酬review和部门预算分配，HRBP需要每个部门的薪资全貌——平均薪资看整体水平、最高薪资看天花板、员工人数看团队规模。这些数据来自不同的业务表（员工主数据在HR系统、薪资在薪酬系统），三表关联是数据仓库中最常见的查询场景，考察的就是 JOIN 的基本功和对聚合函数的理解。

业务场景：年终调薪季，HR需要一份"各部门薪资盘点表"，包含部门名称、人数、平均薪资、最高薪资，用于横向对比各部门的薪酬竞争力。这道题就是这份报表的 SQL 实现。

二、题目

现有三张表：员工表、部门表、薪资表。请统计每个部门的平均薪资、最高薪资和员工人数。

员工表 t6_employee：

+--------+--------+----------+-------------+
| emp_id | emp_name| dept_id  | hire_date   |
+--------+--------+----------+-------------+
| 001    | 张三    | D01      | 2020-01-01  |
| 002    | 李四    | D01      | 2020-06-01  |
| 003    | 王五    | D02      | 2021-03-01  |
| 004    | 赵六    | D02      | 2022-01-01  |
+--------+--------+----------+-------------+

部门表 t6_dept：dept_id=D01,dept_name=技术部; D02=市场部

薪资表 t6_salary：

+--------+--------+
| emp_id | salary |
+--------+--------+
| 001    | 30000  |
| 002    | 25000  |
| 003    | 20000  |
| 004    | 18000  |
+--------+--------+

三、思路分析

这是多表关联 + 聚合统计的基础题型，核心是 LEFT JOIN 拼宽 + GROUP BY 聚合：

1. 驱动表选择：以部门表为主表，保证即使某部门没有员工也能出现（LEFT JOIN）
2. 级联 JOIN：部门 → 员工 → 薪资，三表通过外键关联（dept_id, emp_id）
3. 聚合统计：按 dept_id + dept_name 分组，COUNT(DISTINCT) 数人数，AVG/MAX 算薪资

维度	评分
题目难度	⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

四、逐步推导

步骤1：三表 JOIN 拼接宽表

从部门表出发，LEFT JOIN 员工表获取员工归属，再 LEFT JOIN 薪资表获取薪资数据。

执行SQL

select d.dept_id, d.dept_name,
       e.emp_id, e.emp_name,
       s.salary
from t6_dept d
left join t6_employee e on d.dept_id = e.dept_id
left join t6_salary s on e.emp_id = s.emp_id

执行结果

+---------+-----------+--------+----------+--------+
| dept_id | dept_name | emp_id | emp_name | salary |
+---------+-----------+--------+----------+--------+
| D01     | 技术部    | 001    | 张三     | 30000  |
| D01     | 技术部    | 002    | 李四     | 25000  |
| D02     | 市场部    | 003    | 王五     | 20000  |
| D02     | 市场部    | 004    | 赵六     | 18000  |
+---------+-----------+--------+----------+--------+

步骤2：按部门聚合统计

对拼接后的宽表按 dept_id 和 dept_name 分组，计算人数、平均薪资和最高薪资。

执行SQL

select d.dept_id, d.dept_name,
       count(distinct e.emp_id) as emp_cnt,
       round(avg(s.salary), 2) as avg_salary,
       max(s.salary) as max_salary
from t6_dept d
         left join t6_employee e on d.dept_id = e.dept_id
         left join t6_salary s on e.emp_id = s.emp_id
group by d.dept_id, d.dept_name

执行结果

+---------+-----------+---------+------------+------------+
| dept_id | dept_name | emp_cnt | avg_salary | max_salary |
+---------+-----------+---------+------------+------------+
| D01     | 技术部    | 2       | 27500.00   | 30000      |
| D02     | 市场部    | 2       | 19000.00   | 20000      |
+---------+-----------+---------+------------+------------+

技术部2人，平均薪资 27500，最高 30000（张三）；市场部2人，平均薪资 19000，最高 20000（王五）。

五、常见坑点

坑1：INNER JOIN 会丢失无员工的部门

如果用 INNER JOIN，当某个部门还没有员工时（比如刚成立的新部门），该部门不会出现在结果中。HR 报表通常要求显示所有部门（包括0人的），所以必须用 LEFT JOIN 以部门表为驱动表。

坑2：COUNT(*) vs COUNT(DISTINCT emp_id)

如果员工表与薪资表不是一一对应（比如一个员工有多条薪资记录，对应不同月份），COUNT(*) 会把同一员工算多次。应用 COUNT(DISTINCT e.emp_id) 确保员工去重计数。

坑3：AVG 忽略 NULL

AVG 函数自动忽略 NULL 值。如果某个部门有员工但没有薪资记录（LEFT JOIN 后 salary 为 NULL），AVG 只计算有薪资的员工，不会把 NULL 当作 0 参与平均。这可能导致"部门2人但平均薪资只算1人"的情况，需要根据业务决定 NULL 是忽略还是用 COALESCE 替换。

六、举一反三

1. 加上薪资中位数：在 AVG 和 MAX 的基础上加上中位数（用 ROW_NUMBER 双向排序法），更全面反映薪资分布
2. 按入职年份分档：增加 CASE WHEN hire_date < '2021-01-01' THEN '老员工' ELSE '新员工' END 维度，统计各部门新老员工的薪资差异
3. 部门薪资极差：MAX(salary) - MIN(salary) 反映部门内部的薪资差距，用于判断是否存在薪资倒挂
4. 全公司汇总行：用 GROUP BY ... WITH ROLLUP 在结果末尾追加全公司的汇总统计行

七、知识点总结

考点	说明
多表 LEFT JOIN	以部门表为驱动表，级联关联员工表和薪资表
COUNT DISTINCT	对员工去重计数，避免一人多条薪资记录重复计算
AVG / MAX 聚合	按部门分组计算薪资的平均值和最大值
GROUP BY	按部门 ID 和部门名称分组产出汇总统计

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t6_employee (
    emp_id    string, emp_name string,
    dept_id   string, hire_date string
);
CREATE TABLE t6_dept (dept_id string, dept_name string);
CREATE TABLE t6_salary (emp_id string, salary decimal(10,2));

INSERT INTO t6_employee VALUES ('001','张三','D01','2020-01-01'),('002','李四','D01','2020-06-01'),('003','王五','D02','2021-03-01'),('004','赵六','D02','2022-01-01');
INSERT INTO t6_dept VALUES ('D01','技术部'),('D02','市场部');
INSERT INTO t6_salary VALUES ('001',30000),('002',25000),('003',20000),('004',18000);

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题