排序，查找第N高的薪水

1.第N高的薪水（简单）

1.1 单表查询limit 

select Salary from Employee
group by Salary
order by Salary desc
limit N,1

#limit 用法

limit m,n 从m+1条数据开始取，取n条

例如查询8条数据，索引从5到12，即第6条记录到第13条记录

select * from A limit 5,8; limit 相当于跳过5条

1.2 子查询 

select distinct e.salary
from employee e
where (select count(distinct salary) from employee 
       where salary > e.salary) = N-1

 1.3 窗口函数

SELECT 
Distinct salary 
From (select salary, 
             dense_rank() over(order by salary desc) rnk 
             From employee) temp
Where rnk = N

 

 

2.部门工资最高的员工（中等）

2.1 窗口函数和临时表

With temp as
        (select *,
        dense_rank() over(partition by E.departmentId order by E.salary DESC)  num
        from Employee E)
select D.name Department,t.name Employee,t.salary
from Department as D
join temp as t
ON t.departmentId = D.id
where t.num = 1   --查找工资前N高的员工 where t.num <= N

便于理解，逻辑简单

#dense_rank() 同薪同名，相当于排序以后distinct功能