18.题目描述
对所有员工的当前(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
));
本题的主要思想是复用salaries表进行比较排名,具体思路如下:
select s1.emp_no,s1.salary,count(distinct s2.salary)as rank
from salaries as s1,salaries as s2
where s1.to_date='9999-01-01' and