题解 | #对所有员工的薪水按照salary降序进行1-N的排名#
对所有员工的薪水按照salary降序进行1-N的排名
http://www.nowcoder.com/practice/b9068bfe5df74276bd015b9729eec4bf
select emp_no,salary,t_rank from salaries
natural join
(select s2.salary,count(s2.salary) as t_rank from
(select DISTINCT salary from salaries) as s1,
(select DISTINCT salary from salaries) as s2
where s1.salary>=s2.salary
GROUP by salary) as t1
natural join
(select s2.salary,count(s2.salary) as t_rank from
(select DISTINCT salary from salaries) as s1,
(select DISTINCT salary from salaries) as s2
where s1.salary>=s2.salary
GROUP by salary) as t1
order by salary desc;
先对工资使用self-join进行排序，然后使用natural join。
投递腾讯等公司10个岗位
