牛客SQL练习第23题

题目描述

对所有员工的当前(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`));

解答：

对排名的处理可以在前面的18题找到方法

select s1.emp_no,s1.salary ,count(distinct s2.salary) rank
from salaries s1,salaries 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
order by s1.salary desc ,s1.emp_no asc