所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名
对所有员工的薪水按照salary进行按照1-N的排名
http://www.nowcoder.com/questionTerminal/b9068bfe5df74276bd015b9729eec4bf
不使用窗口函数
-- rank排名:查询表中大于自己薪水的员工的数量(考虑并列:去重) SELECT s1.emp_no, s1.salary, (SELECT COUNT(DISTINCT s2.salary) FROM salaries s2 WHERE s2.to_date = '9999-01-01' AND s2.salary >= s1.salary) AS `rank` -- 去重:计算并列排名 FROM salaries s1 WHERE s1.to_date = '9999-01-01' ORDER BY s1.salary DESC, s1.emp_no ;
使用窗口函数
SELECT emp_no, salary, dense_rank () over ( ORDER BY salary DESC) AS `rank` FROM salaries WHERE to_date = '9999-01-01' ;