615. 平均工资：部门与公司比较#三种方法

目录

• 题目和要求
• • 1. 题目代码
  • 2. 解题分析图览
  • • 方法1：
    • 方法2：
    • 方法3：
  • 3. 难点分析
  • 4. 答案代码以及pretty表格解释
  • 5. 关键总结

题目和要求

表：Salary

+-------------+------+
| 列名        | 类型 |
+-------------+------+
| id          | int  |
| employee_id | int  |
| amount      | int  |
| pay_date    | date |
+-------------+------+
在 SQL 中，id 是该表的主键列。
该表的每一行表示一个员工一个月的薪资。
employee_id 是来自 Employee 表的外键（reference 列）。
 

表： Employee

+---------------+------+
| 列名          | 类型 |
+---------------+------+
| employee_id   | int  |
| department_id | int  |
+---------------+------+
在 SQL 中，employee_id 是该表的主键列。
该表的每一行表示一个员工所属的部门。
 

找出各个部门员工的平均薪资与公司平均薪资之间的比较结果（更高 / 更低 / 相同）。

以 任意顺序 返回结果表。

结果格式如下所示。

 

示例 1：

输入：
Salary 表:
+----+-------------+--------+------------+
| id | employee_id | amount | pay_date   |
+----+-------------+--------+------------+
| 1  | 1           | 9000   | 2017/03/31 |
| 2  | 2           | 6000   | 2017/03/31 |
| 3  | 3           | 10000  | 2017/03/31 |
| 4  | 1           | 7000   | 2017/02/28 |
| 5  | 2           | 6000   | 2017/02/28 |
| 6  | 3           | 8000   | 2017/02/28 |
+----+-------------+--------+------------+
Employee 表:
+-------------+---------------+
| employee_id | department_id |
+-------------+---------------+
| 1           | 1             |
| 2           | 2             |
| 3           | 2             |
+-------------+---------------+
输出：
+-----------+---------------+------------+
| pay_month | department_id | comparison |
+-----------+---------------+------------+
| 2017-02   | 1             | same       |
| 2017-03   | 1             | higher     |
| 2017-02   | 2             | same       |
| 2017-03   | 2             | lower      |
+-----------+---------------+------------+
解释：
在三月，公司的平均工资是 (9000+6000+10000)/3 = 8333.33...
部门 '1' 的平均薪资是 9000，因为该部门只有一个员工，其员工号为 '1'。因为 9000 > 8333.33，所以比较结果为 'higher'
部门 '2' 的平均薪资是（6000 + 10000）/ 2 = 8000，该平均薪资是员工号 '2' 和 '3' 的薪资的平均值。因为 8000 < 8333.33，比较结果为 'lower'。

根据同样的公式，对于二月份的平均薪资比较，结果为 'same'，因为部门 '1' 和 '2' 都与公司的平均薪资相同，即为 7000。

1. 题目代码
2. 解题分析图览
3. 难点分析
4. 答案代码以及pretty表格解释
5. 关键总结

1. 题目代码

Create table If Not Exists Salary (id int, employee_id int, amount int, pay_date date);
Create table If Not Exists Employee (employee_id int, department_id int);
Truncate table Salary;
insert into Salary (id, employee_id, amount, pay_date) values ('1', '1', '9000', '2017/03/31');
insert into Salary (id, employee_id, amount, pay_date) values ('2', '2', '6000', '2017/03/31');
insert into Salary (id, employee_id, amount, pay_date) values ('3', '3', '10000', '2017/03/31');
insert into Salary (id, employee_id, amount, pay_date) values ('4', '1', '7000', '2017/02/28');
insert into Salary (id, employee_id, amount, pay_date) values ('5', '2', '6000', '2017/02/28');
insert into Salary (id, employee_id, amount, pay_date) values ('6', '3', '8000', '2017/02/28');
Truncate table Employee;
insert into Employee (employee_id, department_id) values ('1', '1');
insert into Employee (employee_id, department_id) values ('2', '2');
insert into Employee (employee_id, department_id) values ('3', '2');

2. 解题分析图览

方法1：

方法2：

方法3：

3. 难点分析

• 理解如何使用子查询和窗口函数来计算分组的平均值。
• 理解如何将部门的平均工资与公司的平均工资进行比较。
• 理解如何使用 SQL 的 CASE 语句来实现条件判断。

4. 答案代码以及pretty表格解释

-- 方法1 子查询,逐步拆分
select 
    公司各部门.日期 pay_month
     , department_id
     ,case
 when 每月每部门avg工资 >公司每月平均工资 then 'higher'
 when 每月每部门avg工资 <公司每月平均工资 then 'lower'
 else 'same'
end as comparison
from
(
 select department_id,
        avg(amount) as 每月每部门avg工资,
        left(pay_date,7) as 日期
 from salary join employee on salary.employee_id = employee.employee_id
 group by department_id, 日期
) as 公司各部门
join
(
 select avg(amount) as 公司每月平均工资,  left(pay_date,7) as 日期 from salary group by left(pay_date,7)
) as 公司整体
on 公司各部门.日期 = 公司整体.日期;

+---------+-------+
|公司每月平均工资 |日期| select avg(amount) as 公司每月平均工资,  left(pay_date,7) as 日期 from salary group by left(pay_date,7);
+---------+-------+
|8333.3333|2017-03|
|7000.0000|2017-02|
+---------+-------+
+-------------+----------+-------+
|department_id|每月每部门avg工资|日期|select department_id,
+-------------+----------+-------+       avg(amount) as 每月每部门avg工资,
|1            |9000.0000 |2017-03|       left(pay_date,7) as 日期
|1            |7000.0000 |2017-02|from salary join employee on salary.employee_id = employee.employee_id
|2            |8000.0000 |2017-03|group by department_id, 日期;
|2            |7000.0000 |2017-02|
+-------------+----------+-------+
最后日期相等,case判断
+-------------+----------+-------+---------+-------+    when 每月每部门avg工资 >公司每月平均工资 then 'higher'
|department_id|每月每部门avg工资|日期|公司每月平均工资|日期|   when 每月每部门avg工资 <公司每月平均工资 then 'lower'
+-------------+----------+-------+---------+-------+    else 'same'
|1            |9000.0000 |2017-03|8333.3333|2017-03|    higher
|2            |8000.0000 |2017-03|8333.3333|2017-03|    lower
|1            |7000.0000 |2017-02|7000.0000|2017-02|    same
|2            |7000.0000 |2017-02|7000.0000|2017-02|    same
+-------------+----------+-------+---------+-------+
+-------+-------------+----------+
|pay_month|department_id|comparison| 输出结果
+-------+-------------+----------+
|2017-03|1            |higher    |
|2017-03|2            |lower     |
|2017-02|1            |same      |
|2017-02|2            |same      |
+-------+-------------+----------+
;
-- 方法2
SELECT distinct
        left(pay_date,7) pay_month,
        department_id,
       CASE
            WHEN a.每月每部门avg工资 > a.公司每月平均工资 THEN 'higher'
            WHEN a.每月每部门avg工资 < a.公司每月平均工资 THEN 'lower'
            ELSE 'same'
       END AS "comparison"
FROM (
         SELECT
                salary.pay_date,
                department_id,
                AVG(amount) OVER(PARTITION BY left(pay_date,7)) AS 公司每月平均工资,
                AVG(salary.amount) OVER(PARTITION BY employee.department_id, left(pay_date,7)) AS 每月每部门avg工资
         FROM salary
         right join employee
         ON salary.employee_id = employee.employee_id
     ) a;
+----------+-------------+---------+----------+
|pay_date  |department_id|公司每月平均工资 |每月每部门avg工资|
+----------+-------------+---------+----------+
|2017-02-28|1            |7000.0000|7000.0000 |
|2017-03-31|1            |8333.3333|9000.0000 |
|2017-02-28|2            |7000.0000|7000.0000 |
|2017-02-28|2            |7000.0000|7000.0000 |
|2017-03-31|2            |8333.3333|8000.0000 |
|2017-03-31|2            |8333.3333|8000.0000 |
+----------+-------------+---------+----------+
+---------+-------------+----------+
|pay_month|department_id|comparison|
+---------+-------------+----------+
|2017-02  |1            |same      |
|2017-03  |1            |higher    |
|2017-02  |2            |same      |
|2017-03  |2            |lower     |
+---------+-------------+----------+

;
-- 方法3 avg 有一点点长,有局限,看看就行,了解一下
select distinct
    left(pay_date,7) pay_month  ,department_id
,case                                                                                                                                                              -- max也可以
when left(pay_date,7)='2017-01' and avg(amount) over (partition by left(pay_date,7),department_id) > (select avg(amount) from salary group by left(pay_date,7) having min(left(pay_date,7))='2017-01') then 'higher'-- 1月
when left(pay_date,7)='2017-01' and avg(amount) over (partition by left(pay_date,7),department_id) < (select avg(amount) from salary group by left(pay_date,7) having min(left(pay_date,7))='2017-01') then 'lower'
when left(pay_date,7)='2017-01' and avg(amount) over (partition by left(pay_date,7),department_id) = (select avg(amount) from salary group by left(pay_date,7) having min(left(pay_date,7))='2017-01') then 'same'
when left(pay_date,7)='2017-02' and avg(amount) over (partition by left(pay_date,7),department_id) > (select avg(amount) from salary group by left(pay_date,7) having min(left(pay_date,7))='2017-02') then 'higher'-- 2月
when left(pay_date,7)='2017-02' and avg(amount) over (partition by left(pay_date,7),department_id) < (select avg(amount) from salary group by left(pay_date,7) having min(left(pay_date,7))='2017-02') then 'lower'
when left(pay_date,7)='2017-02' and avg(amount) over (partition by left(pay_date,7),department_id) = (select avg(amount) from salary group by left(pay_date,7) having min(left(pay_date,7))='2017-02') then 'same'
when left(pay_date,7)='2017-03' and avg(amount) over (partition by left(pay_date,7),department_id) > (select avg(amount) from salary group by left(pay_date,7) having min(left(pay_date,7))='2017-03') then 'higher'-- 3月
when left(pay_date,7)='2017-03' and avg(amount) over (partition by left(pay_date,7),department_id) < (select avg(amount) from salary group by left(pay_date,7) having min(left(pay_date,7))='2017-03') then 'lower'
when left(pay_date,7)='2017-03' and avg(amount) over (partition by left(pay_date,7),department_id) = (select avg(amount) from salary group by left(pay_date,7) having min(left(pay_date,7))='2017-03') then 'same'
    -- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------类推
        -- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    -- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    end comparison
from employee e
left join Salary S on e.employee_id = S.employee_id;

5. 关键总结

• 掌握 SQL 中的子查询和窗口函数的使用。
• 理解如何在 SQL 中进行分组计算和条件判断。
• 熟悉 SQL 的 JOIN 操作，特别是在多表查询中的应用。
• 理解如何使用 CASE 语句进行条件逻辑处理。