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

数据仓库晨曦

发布于 2026-07-06 13:32:58

一、题目背景

这道题来自阿里巴巴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  | dept_name  |
+----------+------------+
| D01      | 技术部        |
| D02      | 市场部        |
+----------+------------+

薪资表 t6_salary：

+---------+-----------+
| emp_id  |  salary   |
+---------+-----------+
| 001     | 30000.00  |
| 002     | 25000.00  |
| 003     | 20000.00  |
| 004     | 18000.00  |
+---------+-----------+

三、思路分析

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

驱动表选择：以部门表为主表，保证即使某部门没有员工也能出现（LEFT JOIN）
级联 JOIN：部门 → 员工 → 薪资，三表通过外键关联（dept_id, emp_id）
聚合统计：按 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      | 技术部        | 002     | 李四        | 25000.00  |
| D01      | 技术部        | 001     | 张三        | 30000.00  |
| D02      | 市场部        | 004     | 赵六        | 18000.00  |
| D02      | 市场部        | 003     | 王五        | 20000.00  |
+----------+------------+---------+-----------+-----------+
4 rows selected (0.754 seconds)(https://www.dwsql.com)

步骤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  |
+----------+------------+----------+-------------+-------------+
| D02      | 市场部        | 2        | 19000.00    | 20000.00    |
| D01      | 技术部        | 2        | 27500.00    | 30000.00    |
+----------+------------+----------+-------------+-------------+
2 rows selected (1.921 seconds)(https://www.dwsql.com)

五、常见坑点

坑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 替换。

六、举一反三

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

七、知识点总结

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

八、建表语句和数据插入

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);

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-07-04，如有侵权请联系 cloudcommunity@tencent.com 删除

统计