题解 | 多条件排名/rank()

# 将不重复的薪水挑选出来 diss
select distinct salary
from salaries

# 对diss的薪水进行排序，orderdiss
select 
	diss.salary as salary, 
	row_number() over(order by diss.salary desc) as t_rank
from (
	select distinct salary
	from salaries
) diss

# 连接orderdiss和salaries
select 
	s.emp_no as emp_no,
	orderdiss.salary as salary,
	orderdiss.t_rank as t_rank
from (
	select 
		diss.salary as salary, 
		row_number() over(order by diss.salary desc) as t_rank
	from (
		select distinct salary
		from salaries
	) diss
) orderdiss join (
	select * from salaries order by emp_no
) s on s.salary=orderdiss.salary
order by t_rank

# dense_rank()
select emp_no, salary, 
	dense_rank() over(order by salary desc) as t_rank
from salaries
order by t_rank, emp_no asc