查找在职员工自入职以来的薪水涨幅情况【SQL练习题】

描述

有一个员工表employees简况如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	2001-06-22
10002	1964-06-02	Bezalel	Simmel	F	1999-08-03

有一个薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	85097	2001-06-22	2002-06-22
10001	88958	2002-06-22	9999-01-01
10002	72527	1999-08-03	2000-08-02
10002	72527	2000-08-02	2001-08-02

请你查找在职员工自入职以来的薪水涨幅情况(注意这里强调的是在职员工)，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序，以上例子输出为

（注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01'时，表示依然在职，无后续调整记录）

emp_no	growth
10001	3861

示例1

输入：

drop table if exists  `employees` ; 
drop table if exists  `salaries` ;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','2001-06-22');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1999-08-03');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');

输出：

emp_no|growth
10001|3861

方案一

-- 首先找出在职员工
WITH current_employees AS (
    SELECT s.emp_no
    FROM salaries s
    WHERE s.to_date = '9999-01-01'
),
-- 然后找出每个在职员工入职时的薪水
starting_salaries AS (
    SELECT e.emp_no, s.salary AS start_salary
    FROM employees e
    JOIN salaries s ON e.emp_no = s.emp_no AND e.hire_date = s.from_date
    JOIN current_employees ce ON e.emp_no = ce.emp_no
),
-- 接着找出每个在职员工当前的薪水
current_salaries AS (
    SELECT s.emp_no, s.salary AS current_salary
    FROM salaries s
    JOIN current_employees ce ON s.emp_no = ce.emp_no
    WHERE s.to_date = '9999-01-01'
)
-- 最后计算薪水涨幅并排序
SELECT ss.emp_no, cs.current_salary - ss.start_salary AS growth
FROM starting_salaries ss
JOIN current_salaries cs ON ss.emp_no = cs.emp_no
ORDER BY growth ASC;

代码解释：

1. current_employees 公共表表达式（CTE）：
   • 从 salaries 表中筛选出 to_date 为 '9999-01-01' 的员工编号，这些员工即为在职员工。
2. starting_salaries 公共表表达式（CTE）：
   • 将 employees 表和 salaries 表进行连接，条件是员工编号相同且入职日期等于薪水记录的起始日期。
   • 同时，只保留 current_employees 中的在职员工记录，从而得到每个在职员工入职时的薪水。
3. current_salaries 公共表表达式（CTE）：
   • 将 salaries 表和 current_employees 进行连接，筛选出 to_date 为 '9999-01-01' 的记录，得到每个在职员工当前的薪水。
4. 最终查询：
   • 将 starting_salaries 和 current_salaries 进行连接，计算每个在职员工的薪水涨幅（当前薪水减去入职时的薪水）。
   • 按照薪水涨幅进行升序排序，并返回员工编号和薪水涨幅。

方案二

解题思路：

1. 筛选在职员工：根据 salaries 表中的 to_date = '9999-01-01' 来确定哪些员工是当前在职的。
2. 获取员工的工资记录：我们需要得到这些员工的所有薪水记录。
3. 提取每个员工的入职工资和当前工资：使用 RANK() 窗口函数来为每个员工的薪水记录排名，找到入职时的第一份工资和当前的工资。
4. 计算薪水涨幅：计算员工从入职以来的薪水差异（涨幅），然后按涨幅升序排序。

-- 获取在职员工的薪水记录
WITH employed AS (
    SELECT emp_no 
    FROM salaries 
    WHERE to_date = '9999-01-01'  -- 在职员工的条件
),
-- 获取在职员工的所有薪水记录
employed_salaries AS (
    SELECT s.emp_no, s.salary, s.from_date, s.to_date, e.hire_date
    FROM salaries s
    JOIN employees e ON s.emp_no = e.emp_no
    WHERE s.emp_no IN (SELECT emp_no FROM employed)  -- 只查询在职员工的薪水记录
),
-- 使用 RANK() 为每个员工的薪水记录排序
ranked_salaries AS (
    SELECT 
        emp_no,
        salary,
        from_date,
        RANK() OVER (PARTITION BY emp_no ORDER BY from_date ASC) AS start_rank,  -- 入职时的工资
        RANK() OVER (PARTITION BY emp_no ORDER BY from_date DESC) AS current_rank  -- 当前的工资
    FROM employed_salaries
),
-- 提取入职工资和当前工资
salaries_comparison AS (
    SELECT 
        emp_no,
        MAX(CASE WHEN start_rank = 1 THEN salary END) AS start_salary,  -- 入职时的工资
        MAX(CASE WHEN current_rank = 1 THEN salary END) AS current_salary  -- 当前工资
    FROM ranked_salaries
    GROUP BY emp_no  -- 按员工编号分组
)
-- 计算薪水涨幅并按升序排序
SELECT 
    emp_no, 
    (current_salary - start_salary) AS growth  -- 计算涨幅
FROM salaries_comparison
ORDER BY growth ASC;  -- 按涨幅升序排序

代码解析

1. employed 公共表表达式（CTE）

WITH employed AS (
    SELECT emp_no 
    FROM salaries 
    WHERE to_date = '9999-01-01'  -- 在职员工的条件
)

• 功能：从 salaries 表中筛选出所有在职员工的员工编号 emp_no。在这个数据库设计里，to_date 为 '9999-01-01' 通常被用来表示该员工当前处于在职状态，即这份工资记录是该员工当前的工资信息。
• 结果：返回一个包含所有在职员工编号的临时结果集。

2. employed_salaries 公共表表达式（CTE）

employed_salaries AS (
    SELECT s.emp_no, s.salary, s.from_date, s.to_date, e.hire_date
    FROM salaries s
    JOIN employees e ON s.emp_no = e.emp_no
    WHERE s.emp_no IN (SELECT emp_no FROM employed)  -- 只查询在职员工的薪水记录
)

• 功能：将 salaries 表和 employees 表进行连接，通过 emp_no 字段关联。然后从连接后的结果中筛选出员工编号在 employed CTE 结果集中的记录，也就是只获取在职员工的薪水记录，同时还包含了员工的入职日期 hire_date。
• 结果：返回一个包含在职员工的员工编号、薪水、薪水生效日期、薪水结束日期以及入职日期的临时结果集。

3. ranked_salaries 公共表表达式（CTE）

ranked_salaries AS (
    SELECT 
        emp_no,
        salary,
        from_date,
        RANK() OVER (PARTITION BY emp_no ORDER BY from_date ASC) AS start_rank,  -- 入职时的工资
        RANK() OVER (PARTITION BY emp_no ORDER BY from_date DESC) AS current_rank  -- 当前的工资
    FROM employed_salaries
)

• 功能：对 employed_salaries CTE 的结果集使用窗口函数 RANK()。
  • RANK() OVER (PARTITION BY emp_no ORDER BY from_date ASC)：按照员工编号进行分组（PARTITION BY emp_no），在每个分组内按照 from_date 升序排序，为每条记录分配一个排名，排名为 1 的记录对应的就是员工入职时的工资，将这个排名命名为 start_rank。
  • RANK() OVER (PARTITION BY emp_no ORDER BY from_date DESC)：同样按照员工编号分组，但是在每个分组内按照 from_date 降序排序，排名为 1 的记录对应的就是员工当前的工资，将这个排名命名为 current_rank。
• 结果：返回一个包含员工编号、薪水、薪水生效日期、入职工资排名和当前工资排名的临时结果集。

4. salaries_comparison 公共表表达式（CTE）

salaries_comparison AS (
    SELECT 
        emp_no,
        MAX(CASE WHEN start_rank = 1 THEN salary END) AS start_salary,  -- 入职时的工资
        MAX(CASE WHEN current_rank = 1 THEN salary END) AS current_salary  -- 当前工资
    FROM ranked_salaries
    GROUP BY emp_no  -- 按员工编号分组
)

• 功能：对 ranked_salaries CTE 的结果集按照员工编号进行分组（GROUP BY emp_no）。使用 CASE 表达式结合 MAX() 函数来提取每个员工的入职工资和当前工资。
  • MAX(CASE WHEN start_rank = 1 THEN salary END)：当 start_rank 为 1 时，选取对应的薪水作为入职工资，由于每组只有一个 start_rank 为 1 的记录，所以用 MAX() 函数只是为了聚合结果。
  • MAX(CASE WHEN current_rank = 1 THEN salary END)：当 current_rank 为 1 时，选取对应的薪水作为当前工资。
• 结果：返回一个包含员工编号、入职工资和当前工资的临时结果集。

5. 最终查询

SELECT 
    emp_no, 
    (current_salary - start_salary) AS growth  -- 计算涨幅
FROM salaries_comparison
ORDER BY growth ASC;  -- 按涨幅升序排序

• 功能：从 salaries_comparison CTE 的结果集中选取员工编号和薪水涨幅（当前工资减去入职工资），并将涨幅命名为 growth。然后按照 growth 进行升序排序。
• 结果：返回一个包含员工编号和薪水涨幅的结果集，且按照涨幅从小到大排列。