27
给出每个员工每年薪水涨幅超过5000的员工编号emp_no
http://www.nowcoder.com/questionTerminal/eb9b13e5257744db8265aa73de04fd44
题目：汇给出每个员工每年薪水涨幅超过5000的员工编号emp_no，薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。
提示：在sqlite中获取datetime时间对应的年份函数为strftime(‘%Y’, to_date)
方法：
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.from_date) - STRFTIME('%Y', s1.from_date) = 1
OR STRFTIME('%Y', s2.to_date) - STRFTIME('%Y', s1.to_date) = 1)
AND salary_growth>5000
ORDER BY salary_growth DESC;
注意：因为要的是薪水变更开始日期，所以是s2.from_date。
补充：虽然只用STRFTIME('%Y', s2.to_date) - STRFTIME('%Y', s1.to_date) = 1就能通过题库测试，但是按照上面的写法更保险
补充：在sqlite中截取年份的函数是strftime，而在mysql中则是DATE_FORMAT(to_date, '%Y'):
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 (DATE_FORMAT(s2.from_date, '%Y')-DATE_FORMAT(s1.from_date, '%Y')=1
OR DATE_FORMAT(s2.to_date, '%Y')-DATE_FORMAT(s1.to_date, '%Y')=1)
AND s2.salary-s1.salary>5000
ORDER BY salary_growth DESC;
注意：在mysql中，WHERE查询中的s2.salary-s1.salary不能改成salary_growth
想说一下这题出的确实不好：
因为存在这样的记录：
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10008 | 46671 | 1998-03-11 | 1999-03-11 |
| 10008 | 48584 | 1999-03-11 | 2000-03-10 |
| 10008 | 52668 | 2000-03-10 | 2000-07-31 |
经过筛选| 10008 | 46671 | 1998-03-11 | 1999-03-11 |和| 10008 | 52668 | 2000-03-10 | 2000-07-31 |(to_date差1年)
会认为这次涨幅超过5000(52668-46671)。但实际上看上去这就不是差了1年。(当然要通过题库，这一条数据也是必须查录的)。所以觉得这题不太好。
