关于MySQL中RANK（）函数报错

题目：请你获取薪水第二多的员工的emp_no以及其对应的薪水salary，
若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序。

mysql语句：
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');

SELECT a.emp_no, a.salary -- 再套一层查询
FROM(
SELECT emp_no, salary, dense_rank()over(ORDER BY salary DESC)rank_num
FROM salaries
WHERE to_date = '9999-01-01') as a
WHERE a.rank_num = 2;

报错如下：
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ORDER BY salary DESC)rank_num
FROM salaries
WHERE to_date = '9999-01-01')a
WHER' at line 3

请问怎么改这个报错o(╥﹏╥)o，非常感谢

在mysql 8.0版本，直接用你的脚本测试，没有报错

因此你mysql的版本可能是8以前的老版本，老版本不支持开窗函数的

select e.emp_no, s.salary, e.last_name, e.first_name
from employees as e, salaries as s
where e.emp_no = s.emp_no
and s.salary =( select max(salary) # 此时的最大值就是第二多的薪水
                from salaries where to_date = '9999-01-01'
                and salary != (select max(salary)  # 排除掉最大值
                               from salaries
                               where to_date = '9999-01-01'))
and s.to_date = '9999-01-01';

您好，我是有问必答小助手，您的问题已经有小伙伴帮您解答，感谢您对有问必答的支持与关注！
PS：问答VIP年卡 【限时加赠：IT技术图书免费领】，了解详情>>> https://vip.csdn.net/askvip?utm_source=1146287632