数据:https://blog.csdn.net/qq_38560351/article/details/106331612
题目:
1. 对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名,相同salary并列且按照emp_no升序排列
MySQL实现方法:相当于Oracle中的dense_rank() over()
方式一:
SET @rank=0;
SET @salary=-1;
SELECT emp_no,salary,
CASE
WHEN @salary = salary THEN @rank
WHEN @salary := salary THEN @rank:=@rank+1
END as rank
FROM salaries
WHERE 1=1
AND to_date='9999-01-01'
ORDER BY salary DESC,emp_no;
方式二(与方式一 的思想相同):
SELECT emp_no,salary,
@rank:=@rank+(@salary<>(@salary:=salary)) rank
FROM salaries as a,
(SELECT @rank:=0,@salary:=-1) as b
WHERE 1=1
AND a.to_date = '9999-01-01'
ORDER BY salary DESC,emp_no;
方式三(相对复杂,自连接):
SELECT a.emp_no,a.salary,
count(DISTINCT b.salary) as rank
FROM salaries as a,salaries as b
WHERE 1=1
AND a.to_date='9999-01-01'
AND b.to_date = '9999-01-01'
AND a.salary<=b.salary
GROUP BY a.emp_no
ORDER BY a.salary DESC,a.emp_no;
Oracle实现方法:
使用dense_rank() over ([partition by * ] order by * [desc]) 方法。
select emp_no,salary,
dense_rank() over(order by salary desc) as rank
from salaries
where 1=1
and to_date = '9999-01-01';