sql 开窗函数

引一下题目： 

引一下题目：获取当前薪水第二多的员工的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