牛客 SQL21 查找在职员工自入职以来的薪水涨幅情况

---

一、题目

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

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

请你查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序，以上例子输出为
（注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01’时，表示依然在职，无后续调整记录）

二、解题思路

1.JOIN ON

（1）使用 employees 和 s1 连接，求出现在职的用户的当前薪资
考虑： 将离职员工过滤
（2）使用 employees 和 s2 连接，求出该用户刚入职时的工资

注意：三表连接

FROM 
(表1 INNER JOIN 表2 
ON 表1.字段号=表2.字段号) 
INNER JOIN 表3 
ON 表1.字段号=表3.字段号
#都是和表一进行连接

select e.emp_no,(s1.salary-s2.salary) as growth
FROM employees e 
     JOIN 
     salaries s1
     ON e.emp_no=s1.emp_no and s1.to_date='9999-01-01'
     JOIN
     salaries s2
     ON e.emp_no=s2.emp_no 
     AND e.hire_date=s2.from_date
ORDER BY growth;

2. 窗口函数

使用窗口函数对每个 emp_no 的 to_date 进行降序排序，找到最后一个 to_date 及该日期的对应的salary。

SELECT s1.emp_no,s2.salary-s1.salary as growth
FROM
(
    SELECT e.emp_no,s.salary
    FROM employees e JOIN salaries s
    ON e.emp_no=s.emp_no
    AND e.hire_date=s.from_date
) s1  #开始雇佣时薪资
JOIN
(
    SELECT e.emp_no,s.salary ,
    DENSE_RANK() OVER(PARTITION BY s.emp_no ORDER BY to_date DESC) rank1,
    s.to_date
    FROM employees e JOIN salaries s
    ON e.emp_no=s.emp_no   
    #注意：rank1=1不能写在这里，因为会先执行WHERE后执行SELECT
) s2  #最后一次薪资
ON s1.emp_no=s2.emp_no
WHERE s2.rank1=1 and s2.to_date='9999-01-01'
ORDER BY growth;

3. 子查询

SELECT emp_no,
   (
    SELECT s.salary- salary         #雇佣时的薪资
    FROM salaries s_t
    WHERE from_date= (
        SELECT hire_date
        FROM employees e_t
        WHERE s_t.emp_no=e_t.emp_no
        ) 
    AND s_t.emp_no=s.emp_no  #将最后的两个表通过emp_no连接
    
    ) as growth
    
    
FROM salaries s  #在职且最后一个薪资
WHERE s.to_date='9999-01-01' 
ORDER BY growth;