首页 > 试题广场 >

给出每个员工对比上一年的薪水涨幅超过5000的员工编号emp

[编程题]给出每个员工对比上一年的薪水涨幅超过5000的员工编号emp

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

给出每个员工对比上一年的薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)

(数据保证每个员工的每条薪水记录to_date-from_date=1年，而且同一员工的下一条薪水记录from_date=上一条薪水记录的to_date)

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 salaries VALUES(10001,52117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03');
INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03');
INSERT INTO salaries VALUES(10003,43616,'1996-12-02','1997-12-02');
INSERT INTO salaries VALUES(10003,43466,'1997-12-02','1998-12-02');

则输出：

emp_no	from_date	salary_growth
10001	1987-06-26	9985

算法知识视频讲解

ciphersaw

本题的难点在于如何理解 每年薪水涨幅，以及复用salaries表求出每年薪水涨幅超过5000的员工，具体思路如下：

1、假设s1是涨薪水前的表，s2是涨薪水后的表，因为每个员工涨薪水的时间不全固定，有可能一年涨两次，有可能两年涨一次，所以每年薪水的涨幅，应该理解为两条薪水记录的from_date相同或to_date相同。

/** 如果只限定to_date相同，则将第三条原始测试数据的52668改成62668时，就会少一条【62668-48584=14084】的记录

INSERT INTO salaries VALUES(10008,46671,'1998-03-11','1999-03-11');

INSERT INTO salaries VALUES(10008,48584,'1999-03-11','2000-03-10');

INSERT INTO salaries VALUES(10008, 62668 ,'2000-03-10','2000-07-31'); **/

2、找到s1与s2符合要求的记录后，用s2的薪水减去s1的薪水，用salary_growth表示，加上限定条件 s1.emp_no = s2.emp_no AND salary_growth > 5000，即同一员工每年涨幅超过5000的记录

3、最后依次输出emp_no、from_date、salary_growth，并以salary_growth逆序排列

SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no = s2.emp_no 
AND salary_growth > 5000
AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1 
     OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )
ORDER BY salary_growth DESC

编辑于 2017-12-08 18:21:09 回复(71)

火盒儿

妈的这题也太鬼畜了

发表于 2017-09-25 20:08:56 回复(24)

牛客4615625号

题意太模糊了

发表于 2017-08-27 21:28:31 回复(3)

天天改名字

每年！！！，怎么解？

发表于 2017-08-08 14:59:58 回复(5)

Rochon

我感觉我已经菜到连题目都看不懂了

发表于 2020-04-22 15:02:52 回复(2)

慧宇Howie

看大家争论的点都在一年的时间用年份去减不合理，然而题目其实规定了：数据保证每个员工的每条薪水记录to_data-from_data=1年，而且同一员工的下一条薪水记录from_data=上一条薪水记录的to_data

也就是说，每次提薪一定是一年期，而且一定是上条记录的to_data 等于这次提薪的from_data，那么问题来了，谁能告诉我，我这样写为啥不能过测试用例？？？

为节约大家时间：s1为提薪后，s2为提薪前一年

select s1.emp_no , s1.from_date , (s1.salary - s2.salary) as salary_growth 
from salaries s1 , salaries s2
where s1.emp_no = s2.emp_no and s1.salary - s2.salary > 5000 
and s1.from_date = s2.to_date
order by salary_growth desc

发表于 2020-06-03 15:31:17 回复(14)

流浪的海科特

我的理解是一年的涨幅应该是同一年内的涨幅，例如2017年年初和年末同一个人的工资涨幅大于5000，即可满足，看了讨论不是很赞同高赞观点。下面给出我这种理解的sql
select A.emp_no as emp_no,A.from_date as from_date,(B.salary-A.salary) as salary_growth
from (
select emp_no,from_date,salary,strftime("%Y",from_date) as yearbegan
from salaries
group by emp_no,strftime("%Y",from_date)
having from_date=min(from_date) ) as A
inner join (
select emp_no,from_date,salary,strftime("Y",from_date) as yearend
from salaries
group by emp_no,strftime("Y",from_date)
having from_date=max(from_date) ) as B
on A.emp_no=B.emp_no and A.yearbegan=B.yearend
where B.salary-A.salary>5000
order by (B.salary-A.salary) desc

发表于 2019-02-17 12:49:22 回复(2)

陸くん

***吧出题的，只用年份减=1,2017年12月31日-2016年1月1日，你跟我说这是一年吗？

发表于 2017-09-21 15:50:04 回复(4)

递归到脑栈溢出

每年的薪水涨幅？？这谁顶得住啊

发表于 2019-10-04 17:04:19 回复(0)

丫酱123

select s2.emp_no, s2.from_date,(s2.salary-s1.salary) as salary_growth
from salaries as s1
join salaries as s2
on s1.emp_no = s2.emp_no
where strftime('%Y', s2.to_date)-strftime('%Y', s1.to_date)=1
and salary_growth > 5000
order by salary_growth desc

两张表内连接，限定为同一名员工，工资变更开始的日期限定为1年，工资差大于5000，按照工资差降序排列。但是有一点不懂？为啥我把where 年限限定改为开始日期，即where strftime('%Y', s2.from_date)-strftime('%Y', s1.from_date)=1就不对了，纳闷。。。

发表于 2019-10-20 15:54:01 回复(5)

offer来了请查收

SELECT s1.emp_no, s2.from_date, s2.salary - s1.salary AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no = s2.emp_no AND strftime('%Y', s2.to_date) - strftime('%Y', s1.to_date) = 1 AND salary_growth > 5000
ORDER BY salary_growth DESC;

发表于 2019-06-23 21:32:12 回复(4)

大树WangMo

解答：

select s1.emp_no,s1.from_date,s1.salary-s2.salary as salary_growth
from salaries s1,salaries s2
where strftime('%Y', s1.to_date)-strftime('%Y', s2.to_date)=1
and s1.salary-s2.salary>5000
and s1.emp_no=s2.emp_no
order by salary_growth desc

总结：

1.理解了select * from s1,s2的作用

2.理解了select后where其实就是过滤一些不满足要求的数据

发表于 2019-07-30 23:31:39 回复(1)

当之无愧–逐梦人

SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1 join salaries AS s2 --一般还是用join连接两张表吧我觉得！！
on s1.emp_no = s2.emp_no
where salary_growth > 5000
AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1
OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )
ORDER BY salary_growth DESC

发表于 2019-07-29 18:38:27 回复(0)

慢牛1111

select s1.emp_no,s2.from_date,(s2.salary-s1.salary) salary_growth
from salaries s1
join salaries s2 on s1.to_date=s2.from_date
where (s2.salary-s1.salary)>5000 and TIMESTAMPDIFF(year,s1.from_date,s2.from_date) =1
order by salary_growth desc

#本地能跑出一样的结果，为什么线上不行？

发表于 2021-01-31 20:47:42 回复(0)

WinnerBabe

select s1.emp_no,s1.from_date,(s1.salary-s2.salary)as salary_growth
from salaries s1,salaries s2
where s1.emp_no=s2.emp_no
and (strftime("%Y",s1.to_date) - strftime("%Y",s2.to_date) = 1
OR strftime("%Y",s1.from_date) - strftime("%Y",s2.from_date) = 1 )
and salary_growth > 5000
order by salary_growth desc

发表于 2021-01-05 13:39:11 回复(0)

Chaos丶

有没有大佬可以把我这个思路优化一下，因为是新手写的太乱了很繁琐不太会写。但是我觉得思路没啥问题哈哈

思路：既然说是每年，我觉得应该是每年涨薪都在5000以上才算。我的思路是查出总共上了几年班，然后在算出这几年总共涨薪多少。然后用涨薪除以干了几年，大于5000说名每年的涨薪都在5000以上。下面是代码我用mysql试了没问题。上班部分算的总共上了几年班下半部分是总共涨薪多少

select q.emp_no,q.c as salary_growth from

(select d.emp_no,(date_format(d.m,"%Y")-date_format(x.m,"%Y"))as dt from
(select emp_no,max(from_date) as m from salaries group by emp_no) as d join
(select emp_no,min(from_date) as m from salaries group by emp_no) as x
on d.emp_no=x.emp_no having dt!=0) as n
join
(select a.emp_no,(a.salary-b.salary) as c from
(select s.emp_no,s.salary from salaries as s
join (select emp_no,max(from_date) as m from salaries group by emp_no) as d
on d.emp_no=s.emp_no where s.from_date = d.m) as a
join (select s.emp_no,s.salary from salaries as s
join (select emp_no,min(from_date) as m from salaries group by emp_no) as x
on x.emp_no=s.emp_no where s.from_date = x.m) as b on a.emp_no = b.emp_no) as q
on q.emp_no = n.emp_no where q.c/n.dt >5000 group by q.emp_no;

发表于 2020-12-16 19:44:09 回复(0)

怠倦的小涛君

select s2.emp_no,s2.from_date,(s2.salary-s1.salary) as salary_growth
from salaries s1 join salaries s2 on s1.emp_no=s2.emp_no and s1.to_date=s2.from_date
where s2.salary-s1.salary > 5000

order by salary_growth desc

题目说了工资都是一年一变，所以不需要用strftime函数

发表于 2020-10-05 19:37:41 回复(0)

在看面经的孤勇者很纠结

select s1.emp_no,s2.from_date,(s2.salary-s1.salary) as salary_growth
from salaries s1
join salaries s2
on s1.emp_no=s2.emp_no
and (strftime('%Y', s2.to_date)-strftime('%Y', s1.to_date))=1
and (s2.salary-s1.salary)>5000
order by salary_growth desc;

发表于 2020-09-23 20:34:48 回复(0)

毛小呆

看得题目糊里糊涂

select  s1.emp_no,
        s2.from_date,
        s2.salary - s1.salary as salary_growth
from    salaries as s1
join    salaries as s2
  on    s1.to_date = s2.from_date 
 and    s1.emp_no = s2.emp_no
where   s2.salary - s1.salary > 5000
order by salary_growth desc

发表于 2020-09-09 16:23:49 回复(0)

杭州的风

求问为何我用(STRFTIME('%Y', s2.from_date) = STRFTIME('%Y', s1.from_date) +1)表示一年就出不了结果。这个和STRFTIME('%Y', s2.from_date) - STRFTIME('%Y', s1.from_date) = 1的区别在哪？？

发表于 2020-08-11 15:53:10 回复(0)