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

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

http://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5

我的解题思路是用员工 现在的工资（记为cur_sal）减去入职时的工资（记为hire_sal）

Step1: 计算出员工入职时的工资

通过内连接（INNER JOIN）

用employee表格中的hire_date = salaries中的from_date 即 e.hire_date = s.from_date

并且employee表格中的emp_no = salaries中的emp_no 即 e.emp_no = s.emp_no

这样能保证，假如有员工在同一天入职，他们的工资也能被准确的提取出来。

SELECT 
	e.emp_no,
	s.salary AS hire_sal
FROM employees AS e
INNER JOIN salaries s
ON e.hire_date = s.from_date
AND e.emp_no = s.emp_no

alt

Step2:计算出员工现在的工资

由于不是所有的员工都为在职员工（即to_date显示为9999-01-01）

因此我用窗口函数（dense_rank）来找到最大的to_date。因为最大的to_date所对应的工资，就是员工入职以来最新的工资。

SELECT 
	DENSE_() OVER (PARTITION BY emp_no ORDER BY to_date DESC) AS rankinng,
	emp_no,
	salary,
	to_date
FROM salaries

alt

可以看到每个员工的最大的to_date日期，排名ranking皆为1。所以当后续计算当前工资的时候，通过添加where ranking=1 就可以得到结果。

SELECT A.salary as cur_sal,
       A.emp_no
FROM
	(SELECT 
		DENSE_RANK() OVER (PARTITION BY emp_no ORDER BY to_date DESC) AS ranking,
		emp_no,
		salary,
		to_date
	FROM salaries) AS A         
    WHERE ranking = 1

alt

Step3:计算每个员工工资的差值

用INNER JOIN连接上文中的两个SELECT语句 ON

SELECT cur_sal.emp_no,
       cur_sal.cur_sal-hire_sal.hire_sal AS grouth
FROM
	(SELECT 
    A.salary AS cur_sal,
    A.emp_no
	FROM
		(SELECT 
			DENSE_RANK() OVER (PARTITION BY emp_no ORDER BY to_date DESC) AS ranking,
			emp_no,
            salary,
            to_date
		FROM salaries) AS A        
		WHERE ranking =1) AS cur_sal  #现在的工资
        
INNER JOIN

	(SELECT 
		e.emp_no,
		s.salary AS hire_sal
	FROM employees AS e
	INNER JOIN
	salaries AS s
	ON e.hire_date = s.from_date
		and e.emp_no = s.emp_no) AS hire_sal  #入职时的工资
        
ON hire_sal.emp_no = cur_sal.emp_no;

最终得出的结果为：

alt