先话
开一个博客记录自己学习和输出的过程~
牛客网刷题
查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
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`));
这题的话题解如下:
SELECT emp_no, COUNT(emp_no) AS t
FROM salaries
GROUP BY emp_no
HAVING t > 15
即将涨幅简单理解为工资那栏即salary的变动(即使降薪或平薪),进而代码体现即为对员工号emp_no的计数,显然这是非常不科学的,故题解答案是有错误的。
下面给出讨论区以及思考后认为正确的答案:
SELECT a.emp_no, COUNT(*) AS t
FROM salaries AS a
INNER JOIN salaries AS b
ON a.emp_no = b.emp_no AND a.to_date = b.from_date
WHERE a.salary < b.salary
GROUP BY a.emp_no
HAVING t>15