首页 > 试题广场 >

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

[编程题]查找在职员工自入职以来的薪水涨幅情况

热度指数：556646 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 32M，其他语言64M
算法知识视频讲解

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

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

输出

emp_no|growth
10001|3861

算法知识视频讲解

C＃ぞ‖

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

发表于 2017-08-17 11:45:21 回复(69)

Mysql

Chen_Vic

select fs.emp_no, ls.salary - fs.salary 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
) fs
join(
    select e.emp_no, s.salary
    from employees e 
    left join salaries s
    using(emp_no) 
    where s.to_date = '9999-01-01'
) ls
using(emp_no)
order by growth;

发表于 2022-01-20 15:19:01 回复(0)

wong-1994

只用salaries一张表的做法

select sal_early.emp_no, (now_sal-start_sal) as growth
from (
    select emp_no, salary start_sal
    from (
        select *,row_number() over(partition by emp_no order by to_date) sal_num
        from salaries
        ) sn
    where sal_num = 1
    ) sal_early
join (
    select emp_no, salary now_sal
    from salaries 
    where to_date = '9999-01-01'
    ) sal_late
on sal_early.emp_no = sal_late.emp_no
order by growth

发表于 2021-04-19 12:03:55 回复(0)

AlphaHerculis.

SELECT sS.emp_no,(sC.Csalary-sS.Ssalary) growth FROM
(SELECT e.emp_no,s.salary Csalary FROM employees e LEFT JOIN salaries s ON e.emp_no=s.emp_no WHERE s.to_date='9999-01-01') sC
INNER JOIN
(SELECT e.emp_no,s.salary Ssalary FROM employees e LEFT JOIN salaries s ON e.emp_no=s.emp_no AND e.hire_date=s.from_date) sS
ON sC.emp_no=sS.emp_no
ORDER BY growth

发表于 2019-08-19 07:29:38 回复(0)

LINDAMAN.

SELECT aa.emp_no,(aa.salary-bb.salary)AS growth FROM
(SELECT a.emp_no,a.salary FROM salaries a JOIN employees e 
ON a.emp_no=e.emp_no AND  to_date= 
(SELECT MAX(to_date) FROM salaries WHERE emp_no=a.emp_no AND to_date='9999-01-01'))AS aa 
JOIN
(SELECT b.emp_no,b.salary FROM salaries b JOIN employees e 
ON b.emp_no=e.emp_no AND  to_date= 
(SELECT MIN(to_date) FROM salaries WHERE emp_no=b.emp_no AND b.from_date=e.hire_date))AS bb 
ON aa.emp_no=bb.emp_no
ORDER BY growth;

发表于 2018-06-01 20:44:19 回复(0)

ciphersaw

本题思路是先分别用两次LEFT JOIN左连接employees与salaries，建立两张表，分别存放员工当前工资（sCurrent）与员工入职时的工资（sStart），再用INNER JOIN连接sCurrent与sStart，最后限定在同一员工下用当前工资减去入职工资。

方法一：内层用LEFT JOIN，外层用INNER JOIN（内层也可以改用 INNER JOIN）

SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT s.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') AS sCurrent
INNER JOIN (SELECT s.emp_no, s.salary FROM employees e LEFT JOIN salaries s ON e.emp_no = s.emp_no WHERE s.from_date = e.hire_date) AS sStart
ON sCurrent.emp_no = sStart.emp_no
ORDER BY growth

方法二：内外都层用FROM并列查询

SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.to_date = '9999-01-01') AS sCurrent,
(SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.from_date = e.hire_date) AS sStart
WHERE sCurrent.emp_no = sStart.emp_no
ORDER BY growth

编辑于 2017-08-03 14:17:13 回复(73)

力喜

对于每个员工，自入职以来的薪水涨幅是：当前的薪水-入职时的薪水。
表是这样的：

需要分别求得入职时的工资与当前时间“ 9999-01-01”对应的工资。
先限定员工号为“ 1”。
拆开来看，先取得当前时间的工资：把员工表e与薪水表通过e.to_date =“ 9999-01-01”用inner join进行连接：（只显示重要列）

SQL 语句：

select e.emp_no,from_date,to_date,salary,hire_date
from employees e inner join salaries s on s.to_date='9999-01-01' and e.emp_no = 1;

接下来，获取入职时的工资，把员工表e与薪水表s通过e.hire_date = s.from_date用内部联接进行连接：

SQL 语句:

select e.emp_no,from_date,to_date,salary,hire_date
from employees e inner join salaries s on
s.from_date=e.hire_date and e.emp_no=1;

以上是分解开来的替代过程，最后用两个表的工资值相减，就是增长。

接下来写成连续的形式，而且要考虑是“所有员工的信息”，所以以emplouees表的emp_no为基准，先需要 employees 表和 salaries 表进行联结，通过 salaries.to_date ='9999-01-01'和employees.emp_no = salaries.emp_no，可以将此工资表重命名为一张表。这样的表的薪水就是当前薪水，然后再继续与薪水进行一次联结，（还是以emplouees表的emp no为基准）通过employee.hire_date = b.from_date和e.emp_no = b.emp_no，这个工资重命名为b表，这样b表的薪水就是入职时薪水，

代码如下：

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

编辑于 2021-03-26 22:40:44 回复(47)

我是如此相信

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

编辑于 2018-11-02 11:13:16 回复(9)

OutOfMemoryError

1.找出每个员工当前工资

select e.emp_no,s.salary as sTo

from employees as e

left join salaries as s on e.emp_no=s.emp_no where s.to_date='9999-01-01'

2.找出每个员工入职时的工资

select e.emp_no,s.salary as sHire from employees as e

left join salaries as s on e.emp_no=s.emp_no and s.from_date=e.hire_date

3.结合找出growth

select t1.emp_no,(t1.sTo-t2.sHire) as growth from

(select e.emp_no,s.salary as sTo from employees as e

left join salaries as s on e.emp_no=s.emp_no where s.to_date='9999-01-01') as t1 join

(select e.emp_no,s.salary as sHire from employees as e

left join salaries as s on e.emp_no=s.emp_no and s.from_date=e.hire_date) as t2 on t1.emp_no=t2.emp_no

order by growth asc;

发表于 2017-07-13 11:45:14 回复(11)

payall4u

select e.emp_no, j.salary - s.salary as growth
from employees e
join salaries s on e.emp_no = s.emp_no and e.hire_date = s.from_date
join salaries j on e.emp_no = j.emp_no and j.to_date = '9999-01-01'
order by growth;

大致思路，我们直接连出来一张表就好了

(id, 来的时候的工资，最后的工资)

这张表第二个字段和第三个字段都涉及到了salary

所以一定会级联两次表salaries
也就是employees join salaries join salaries之后用on把条件筛选一个就可以了。

编辑于 2018-05-27 20:07:47 回复(2)

zz80

select s.emp_no,max(salary)-min(salary) growth
from employees e inner join salaries s
on e.emp_no = s.emp_no
group by s.emp_no

order by growth asc;

这样为什么不行

发表于 2017-09-05 20:30:53 回复(34)

10086123

在这个题，不知道第一个表有什么用

发表于 2018-08-31 20:02:13 回复(9)

1988coding

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

先得到一个当前所有员工的薪水表（employees join salaries on employees.emp_no = salaries.emp_no and salaries.to_date='9999-01-01'）和一个入职以来员工的薪水表(employees join salaries on employees.emp_no = salaires.emp_no and employees.hire_date= salaries.from_date)

然后连接这两个表

接下来就可以计算入职以来的薪水涨幅，并排序

发表于 2017-08-29 23:32:19 回复(6)

uu.king

强！无敌！

select e.emp_no,s1.salary-s2.salary as growth from employees e

inner join salaries s1 on s1.to_date="9999-01-01" and s1.emp_no=e.emp_no
inner join salaries s2 on s2.from_date=e.hire_date and s2.emp_no=e.emp_no
order by growth asc;

发表于 2017-09-28 14:29:39 回复(6)

骑牛路过

为什么我这个通不过啊，我在本地命名测试通过了啊

select a.emp_no,(max(salary)-min(salary)) growth

from employees a join salaries b on a.emp_no=b.emp_no
group by a.emp_no

order by growth;

发表于 2018-09-15 21:40:12 回复(12)

Mysql

Flora.201903132327831

1. 不是薪水不增不显示，而是已经离职的不显示。在后面的测试数据中存在未离职、但是薪水不增，growth也要为0

2. 采用自连接的方法

SELECT a.emp_no,b.salary-a.salary as growth
-- SELECT *
FROM (
    SELECT *, rank() over(partition by emp_no order BY to_date asc) as asc_date
    FROM salaries) as a
INNER JOIN (
    SELECT *,rank() over(partition by emp_no order BY to_date DESC) as desc_date
    FROM salaries) as b
ON a.emp_no=b.emp_no AND desc_date=1 AND asc_date=1
WHERE b.to_date='9999-01-01'
ORDER BY growth

发表于 2021-10-03 23:27:47 回复(0)

Mysql

ch1_cool

自己的理解，仅供参考，如有错误望指出

题目要求为：在职员工的薪水涨幅情况

分三步：

1.在职员工的现阶段薪资表 a
2.员工入职时的薪资表 b
3.连接a,b两表，求出薪资增幅growth,增序排列

在职员工的现阶段薪资表 a

(select s.emp_no,s.salary
from salaries s
where s.to_date = '9999-01-01') as a

员工入职时的薪资表 b

(select s.emp_no,s.salary
from employees e
inner join salaries s
on e.emp_no = s.emp_no and e.hire_date = s.from_date) as b

连接a,b两表，求出薪资增幅growth,增序排列

select a.emp_no,(a.salary - b.salary) as growth
from a
inner join  b
on a.emp_no = b.emp_no
order by growth

发表于 2021-09-13 10:36:23 回复(0)

梦龙吃梦龙

1、关键词：每个员工的薪水上涨、按grow升序排列

2、语句

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

此题的关键在于将每个人的入职薪水和当前薪水表示出来

发表于 2020-08-02 20:28:20 回复(0)

一二三四伍六七

只用salaries表

select
    t1.emp_no,(t2.salary-s.salary) as growth
from
    (select emp_no,min(to_date) as min_date from salaries group by emp_no) t1 
join 
    salaries s
on 
    t1.emp_no=s.emp_no and t1.min_date=s.to_date
join
    (select emp_no,salary from salaries where to_date='9999-01-01') t2
on
    t1.emp_no=t2.emp_no
group by
    t1.emp_no
order by 
    growth asc;

发表于 2020-07-09 18:53:40 回复(2)

~~好好工作~~

基础思路：JOIN 现在员工的临时表与员工最早的salary，再做减法

个人认为这里的查询不应该将hire_date与salaries的from_date强耦合，所以最早的salary通过group by找到每一个员工最早的salary（采用where s.from_date<=s.from_date）

select A.emp_no, A.salary - B.salary AS growth  FROM 
    (select e.emp_no, s.salary from 
         employees as e JOIN salaries as s ON e.emp_no = s.emp_no 
             where s.to_date='9999-01-01' group by e.emp_no) AS A 
JOIN 
    (select e.emp_no, salary from employees as e JOIN salaries as s ON e.emp_no = s.emp_no 
         where s.from_date<=s.from_date group by e.emp_no) AS B 
ON A.emp_no=B.emp_no order by growth ASC;

发表于 2019-07-24 10:00:03 回复(3)