sql实战21:查找所有员工自入职以来的薪水涨幅情况
题目描述:
查找所有员工自入职以来
的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
(注:可能有employees表和salaries表里存在记录的员工,有对应的员工编号和涨薪记录,但是已经离职了,离职的员工salaries表的最新的to_date!='9999-01-01'
,这样的数据不显示在查找结果里面)
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`));
题解:
/*
入职以来的薪水涨幅是:当前的薪水-入职时的薪水
离职的员工salaries表的最新的to_date!='9999-01-01'
薪水中途也有可能会变化
当前的薪水怎么求那?
升序默认就是升序,
*/
select e.emp_no, (s1.salary-s2.salary) as growth
from employees as e
inner join salaries as s1
on e.emp_no=s1.emp_no and s1.to_date='9999-01-01'
inner join salaries as s2
on e.emp_no=s2.emp_no and s2.from_date=e.hire_date
order by growth
题目思路:
自入职以来,工资的涨幅,这道题不是问自入职以来薪水的总涨幅,所以自入职以来的工资=现在工资-入职时的工资
题目中给出了一个条件,离职的员工salaries表的最新的to_date!='9999-01-01'
,这句话的意思是,员工不离职的话,to_date字段给出的是9999-01-01
,离职的员工给出的是准确离职时间
,就像一个字段可以用1表示存在,0表示已删除一样.
1.现在的工资怎么求? 两个表可以内联结,而且salaries 表的to_date!='9999-01-01'
,
2.入职的工资怎么求?这里我们参考的别人的答案,是入职时间和第一次发工资时间相等,即salaries.from_date=employees.hire_date
两次查询的salary,s1.salary-s2.salary
就是所得
3.三张表是如何联结的,可以使用内联结
参考题解
是牛客上的一个叫全世界最好的嘉。
的题解,非常详细,所以贴了过来,如有侵权,必删.
需要分别求得入职时的工资与当前时间”9999-01-01” 对应的工资。先限定员工号为‘1"。拆开来看,先取得当前时间的工资:把employees 表 e与 salaries 表通过 e.to_date=“9999-01-01” 用 inner join 进行连接: (只显示重要列
employee e inner join salaries
on e.to_date=“9999-01-01” and emp_no=1;
接下来,取得入职时的工资,把employees 表 e与 salaries 表 s 通过 e.hire_date=s. from_date
用 inner join 进行连接:
employee e inner join salaries s on e.hire_date=s.from_date and emp_no=1;
以上是拆分开来的求解过程,最后用两个表的 salary 值相减 ,就是 growth
。
接下来写成连续的形式,而且要考虑是“所有员工的信息”,所以以 emplouees 表的emp no 为基准, 先需要employees 表和salaries 表进行联结,通过 salaries.to_date=‘9999-01-01’ and employees.emp_no=salaries.emp_no, 并将这个 salaries 表重命名为 a 表。这样a表的 salary 就是当前薪水,然后再继续与 salaries 进行一次联结,(还是以emplouees 表的emp no 为基准)通过employees.hire_date=b.from_date and e.emp_no=b.emp_no,这个salaries 重命名为 b 表,这样 b 表的薪水就是入职时薪水,