SQL编程：对所有员工的当前薪水按照salary进行1-N的排名

题目描述

对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
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`));

输入描述:

无

输出描述:

emp_no	salary	t_rank
10005	94692	1
10009	94409	2
10010	94409	2
10001	88958	3
10007	88070	4
10004	74057	5
10002	72527	6
10003	43311	7
10006	43311	7
10011	25828	8

方法一：先构建不含salary的rank表，再将rank表和salaries表内接，然后排序得到结果。

select 
a.emp_no,
a.salary,
b.rank
from
salaries as a
inner join
(
    select 
    s1.emp_no,
    count(distinct s2.salary) as rank
    from
    salaries as s1,
    salaries as s2
    where
    s1.to_date='9999-01-01'
    and
    s2.to_date='9999-01-01'
    and
    s1.salary<=s2.salary
    group by 
    s1.emp_no
) as b
on 
a.emp_no=b.emp_no
and
a.to_date='9999-01-01'
order by 
a.salary desc,
a.emp_no asc;

方法二：固定s1的一条记录，利用关联查询到它的rank

select 
s1.emp_no,
s1.salary,
(
    select
    count(distinct s2.salary)
    from
    salaries as s2
    where
    s2.to_date='9999-01-01'
    and
    s2.salary>=s1.salary
) as rank
from
salaries as s1
where
s1.to_date='9999-01-01'
order by
s1.salary desc,
s1.emp_no asc;

方法三：窗口函数的使用（MySQL不支持）

①rank()：在计算排序时，若存在相同位次，会跳过之后的位次。例如：有3条排在第一位时，排序为：1,1,1,4....

②dense_rank()：在计算排序时，若存在相同位次，不会跳过之后的位次。例如，有3条排在第1位时，排序为：1,1,1,2······

③row_number()：这个函数赋予唯一的连续位次。例如，有3条排在第1位时，排序为：1，2，3，4······

select 
emp_no,
salary,
dense_rank()
over
(
    order by
    salary
    desc
) as rank
from 
salaries
where
to_date='9999-01-01'
order by
rank asc,
emp_no asc;