题解 | #查找在职员工自入职以来的薪水涨幅情况#

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

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

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

重难点：自入职以来薪水涨幅=当前薪水-入职薪水

## 筛选在职人员联合表
SELECT *
FROM salaries AS s LEFT JOIN employees AS e
ON s.emp_no = e.emp_no 
WHERE s.to_date = '9999_01-01';

在此基础上构建在职人员当前薪水表

## 构建当前工资表
SELECT s.emp_no, s.salary
FROM salaries AS s LEFT JOIN employees AS e
ON s.emp_no = e.emp_no AND s.from_date=e.hire_date
WHERE s.to_date = '9999_01-01';

构建在职人员入职薪水表：
用薪水开始时间=入职时间作为筛选入职工资的条件
用到职时间选取在职人员的编号，作为筛选在职人员的条件2

## 构建入职工资表
SELECT s.emp_no, s.salary
FROM salaries AS s LEFT JOIN employees AS e
ON s.emp_no = e.emp_no 
WHERE s.from_date=e.hire_date AND 
s.emp_no IN (SELECT s.emp_no
             FROM salaries AS s LEFT JOIN employees AS e
             ON s.emp_no = e.emp_no AND s.from_date=e.hire_date
             WHERE s.to_date = '9999_01-01');

将两份工资表进行联合

## 将当前工资表和入职工资表进行联合
SELECT *
FROM (SELECT s.emp_no, s.salary
     FROM salaries AS s LEFT JOIN employees AS e
     ON s.emp_no = e.emp_no AND s.from_date=e.hire_date
     WHERE s.to_date = '9999_01-01') AS salary_current 

      JOIN

     (SELECT s.emp_no, s.salary
      FROM salaries AS s LEFT JOIN employees AS e
      ON s.emp_no = e.emp_no 
      WHERE s.from_date=e.hire_date AND 
      s.emp_no IN (SELECT s.emp_no
                   FROM salaries AS s LEFT JOIN employees AS e
                   ON s.emp_no = e.emp_no AND s.from_date=e.hire_date
                   WHERE s.to_date = '9999_01-01')) AS salary_hire
      ON salary_current.emp_no = salary_hire.emp_no;

最后根据题目要求提取职员编号，薪水涨幅，并按照薪水涨幅升序排序

## 将当前工资表和入职工资表进行联合
SELECT salary_current.emp_no, (salary_current.salary - salary_hire.salary) AS growth
FROM (SELECT s.emp_no, s.salary
     FROM salaries AS s LEFT JOIN employees AS e
     ON s.emp_no = e.emp_no AND s.from_date=e.hire_date
     WHERE s.to_date = '9999_01-01') AS salary_current 

      JOIN

     (SELECT s.emp_no, s.salary
      FROM salaries AS s LEFT JOIN employees AS e
      ON s.emp_no = e.emp_no 
      WHERE s.from_date=e.hire_date AND 
      s.emp_no IN (SELECT s.emp_no
                   FROM salaries AS s LEFT JOIN employees AS e
                   ON s.emp_no = e.emp_no AND s.from_date=e.hire_date
                   WHERE s.to_date = '9999_01-01')) AS salary_hire
      ON salary_current.emp_no = salary_hire.emp_no

ORDER BY growth ASC;