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

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	2001-06-22
10002	1964-06-02	Bezalel	Simmel	F	1999-08-03

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

emp_no	salary	from_date	to_date
10001	85097	2001-06-22	2002-06-22
10001	88958	2002-06-22	9999-01-01
10002	72527	1999-08-03	2000-08-02
10002	72527	2000-08-02	2001-08-02

请你查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序，以上例子输出为

（注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01'时，表示依然在职，无后续调整记录）

emp_no	growth
10001	3861

示例1

输入:

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');

输出:

10001|3861

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Sqlite 解法, 执行用时: 9ms, 内存消耗: 3380KB, 提交时间: 2021-08-08

select c.emp_no,(d.t-c.st)as growth
from (select a.emp_no,b.salary as st
    from employees a left join salaries b 
    on a.emp_no=b.emp_no
    and a.hire_date=b.from_date
) c 
inner join (select a.emp_no,b.salary as t
            from employees a inner join salaries b 
            on a.emp_no = b.emp_no
            where b.to_date='9999-01-01') d 
on c.emp_no=d.emp_no
order by growth

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3348KB, 提交时间: 2021-09-07

select start.emp_no,(current.salary-start.salary) as growth
from 
    (select s.emp_no,s.salary
    from salaries as s
    inner join employees as e
    on s.emp_no = e.emp_no and s.from_date = e.hire_date) as start
inner join
    (select s.emp_no,s.salary
    from salaries as s
    where s.to_date = '9999-01-01') as current
on start.emp_no = current.emp_no
order by growth

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3352KB, 提交时间: 2021-09-15

select b.emp_no,(b.salary-a.salary) as growth 
from(select e.emp_no,s.salary from employees e left join
     salaries s on e.emp_no = s.emp_no and e.hire_date = s.from_date) a 
inner join (select e.emp_no,s.salary from employees e left join
            salaries s on e.emp_no = s.emp_no 
           where s.to_date = '9999-01-01') b 
on a.emp_no = b.emp_no order by growth

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3360KB, 提交时间: 2021-09-09

select a.emp_no,(b.salary - c.salary) as growth 
from
    employees as a
    inner join salaries as b
    on a.emp_no = b.emp_no and b.to_date = '9999-01-01'
    inner join salaries as c
    on a.emp_no = c.emp_no and a.hire_date = c.from_date
order by growth asc

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3364KB, 提交时间: 2021-09-14

select e.emp_no, (s2.salary-s1.salary) growth
from employees e
join salaries s1 on e.emp_no=s1.emp_no and e.hire_date=s1.from_date
join salaries s2 on e.emp_no=s2.emp_no and s2.to_date='9999-01-01'
order by growth

上一题

下一题

详情