首页 > 试题广场 >

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

[编程题]查找在职员工自入职以来的薪水涨幅情况
  • 热度指数：528273 时间限制：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');

输出

10001|3861
算法知识视频讲解
添加回答
offer冲鸭头像 offer冲鸭
注意过去工资条件s.from_date = e.hire_date
当前工资是s.to_date = '9999-01-01'
发表于 2019-02-25 10:31:18 回复(0)
更多回答
C＃ぞ‖头像 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
毒书破万卷头像 毒书破万卷
将两个salaries表联合，条件为a.emp_no=b.emp_no，将a表——a.to_date='9999-01-01' 筛选在职员工，
b表——(b.emp_no,b.from_date) 
in (select emp_no,min(from_date)
from salaries group by emp_no) 筛选入职时的工资
select 
a.emp_no,(a.salary-b.salary) as growth
from salaries a join salaries b
on a.emp_no=b.emp_no
where a.to_date='9999-01-01'
and (b.emp_no,b.from_date) 
in (select emp_no,min(from_date)
from salaries group by emp_no)
order by growth


发表于 2022-05-04 22:45:23 回复(0)
Sqlite
Stzolo头像 Stzolo
select  a.emp_no,c.salary-b.salary as growth from employees a
left join salaries b  on a.emp_no = b.emp_no  and a.hire_date =b.from_date
left join salaries c  on c.emp_no = b.emp_no  and c.to_date='9999-01-01'
where c.salary is not null
order by growth
发表于 2022-04-15 15:32:18 回复(0)
Mysql
Chen_Vic头像 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头像 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)
ged_2357头像 ged_2357
select s1.emp_no,s2.salary-s1.salary growth
from(select emp_no,salary
     from salaries
     group by emp_no
     having from_date=min(from_date)
    ) s1  -- 入职工资
join (select emp_no,salary
      from salaries
      group by emp_no
      having to_date=max(to_date) and to_date='9999-01-01'
     ) s2-- 在职员工现在的工资
on s1.emp_no=s2.emp_no
order by growth 

发表于 2020-08-19 11:01:23 回复(1)
每天都是学习的小白头像 每天都是学习的小白
发表于 2019-10-19 18:40:02 回复(0)
AlphaHerculis.头像 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.头像 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头像 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)
牛客444760745号头像 牛客444760745号

仅用salaries表完成:

SELECT t1.emp_no, (t1.s1 - t2.s2) AS growth
FROM 
(
    SELECT emp_no, salary AS s1
    FROM salaries
    GROUP BY emp_no
    HAVING to_date = "9999-01-01"
) AS t1,
(
    SELECT emp_no, MIN(salary) AS s2
    FROM salaries
    GROUP BY emp_no
) AS t2
WHERE t1.emp_no = t2.emp_no
ORDER BY growth
发表于 2021-10-02 17:22:06 回复(0)
skinacc头像 skinacc 
 select emp_no, max(salary)-min(salary) as growth, max(to_date) from salaries
 group by emp_no
 having max(to_date)='9999-01-01'
这道题直接这么算行不行？
除非发生了涨薪又降薪的窒息操作，这样算比较快吧
发表于 2021-05-18 17:25:14 回复(1)
我是如此相信头像 我是如此相信 
    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头像 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)
zz80头像 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)
payall4u头像 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 回复(1)
10086123头像 10086123
在这个题，不知道第一个表有什么用
发表于 2018-08-31 20:02:13 回复(9)
1988coding头像 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头像 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)
提交观点

问题信息

难度：
998条回答 45558浏览

热门推荐

通过挑战的用户

查看代码

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号