描述
有一个薪水表salaries简况如下:
对所有员工的薪水按照salary降序进行1-N的排名,要求相同salary并列且按照emp_no升序排列:
解:
select s1.emp_no,s1.salary,
#对于外层s1的每一个salary内层s2的所有salary与之比较,利用count函数可取到每个s1的 salary的排名
(select COUNT(DISTINCT s2.salary) from salaries as s2
where s2.salary>=s1.salary and s1.to_date='9999-01-01') as t_rank
from salaries as s1
where s1.to_date='9999-01-01'
order by s1.salary desc
看了其他人的解答才知道还有【窗口函数】可以用
大佬的方法我copy在下面:
SELECT
emp_no,
salary,
dense_rank () over (
ORDER BY salary DESC) AS `rank`
FROM
salaries
WHERE to_date = '9999-01-01' ;