牛客SQL学习 _18_217 对所有员工的薪水按照salary降序进行1-N的排名

---

一、题目

二、代码

select table2.emp_no, table3.salary,table2.high_times  #呈现
from
( 
    select table1.emp_no, count(distinct table1.high)  as high_times #统计>的薪资
    from
    (
        select sa.emp_no , (sa2.salary ) as high  #双表连接 列出>=的薪资
        from salaries sa , salaries sa2
        where sa2.salary>=sa.salary
    ) table1
    group by table1.emp_no
) table2
join salaries table3
on table2.emp_no = table3.emp_no
order by table3.salary desc,table3.emp_no asc

三、运行结果