查找在职员工自入职以来的薪水涨幅情况
描述
有一个员工表employees简况如下:
有一个薪水表salaries简况如下:
请你查找在职员工自入职以来的薪水涨幅情况,给出在职员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序,以上例子输出为
(注: to_date为薪资调整某个结束日期,或者为离职日期,to_date='9999-01-01'时,表示依然在职,无后续调整记录)
建表:
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');
提交代码:
select startSal.emp_no,currSal.salary - startSal.salary 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)startSal
join
(select e.emp_no,s.salary
from employees e
join salaries s
on e.emp_no = s.emp_no
and s.to_date = '9999-01-01')currSal
on startSal.emp_no = currSal.emp_no
order by growth asc
提交结果:答案正确 运行时间:17ms 占用内存:3452KB 使用语言:Sqlite 用例通过率:100.00%
用子查询和内连接,不用rownumber,涉及到入职时间和当前时间两个阶段的薪水情况,所以单独查询出入职表和当前表两个临时表。
另外,离职人员排除在外,所以需要两个表链接以后取当前时间作为当前表。