引一下题目:
引一下题目:获取当前薪水第二多的员工的emp_no以及其对应的薪水sal_牛客题霸_牛客网
建表
drop table if exists `salaries` ;
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,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
INSERT INTO salaries VALUES(10009,95409,'2002-02-14','9999-01-01');
INSERT INTO salaries VALUES(10010,94692,'2001-11-23','9999-01-01');
INSERT INTO salaries VALUES(10011,25828,'1990-01-22','9999-01-01');
1.rank() over()相同分数排名相同,排名不连续
SELECT emp_no ,salary, RANK() OVER(ORDER BY salary DESC) AS row_num
FROM salaries
WHERE to_date='9999-01-01'
结果:
2.ROW_NUMBER() over()相同分数排名不同,排名连续
SELECT emp_no ,salary, ROW_NUMBER() OVER(ORDER BY salary DESC) AS row_num
FROM salaries
WHERE to_date='9999-01-01'
3.DENSE_RANK() over()相同分数排名相同,排名连续
SELECT emp_no ,salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS row_num
FROM salaries
WHERE to_date='9999-01-01'
本题答案:
SELECT emp_no ,salary
FROM (
SELECT emp_no ,salary, dense_rank() OVER(ORDER BY salary desc) AS row_num
FROM salaries
where to_date='9999-01-01'
) AS r
WHERE row_num=2