窗口函数：排名dense_rank() over、计数count(*) over

 注意：rk不能直接提取出来，需要建立一个临时表，再给rk附上条件

编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据下述给定的表，查询结果应返回：

思路：先分组给出排名，然后挑出排名<=3的。

select *,dense_rank() over
(partition by e.DepartmentId order by e.salary desc) 
as rk from employee e

select d.Name as Department,e.Name as Employee,e.salary as Salary,
       dense_rank() over(partition by e.DepartmentId order by e.salary desc) as rk
from employee e,Department d
where e.DepartmentId=d.id

这里的where的用法以前没有在两个表之间用过，先可以记下来，类似于join。

为了看是否是这样的，做了一下简单改动，结果是一样的。

select d.Name as Department,e.Name as Employee,e.salary as Salary,
       dense_rank() over(partition by e.DepartmentId order by e.salary desc) as rk
from employee e inner join Department d
on e.DepartmentId=d.id

 

 最后一步：根据排名<3，则可以提取出前3名。

select a.Department,a.Employee,a.Salary
from
(select d.Name as Department,e.Name as Employee,e.salary as Salary,
       dense_rank() over(partition by e.DepartmentId order by e.salary desc) as rk
from employee e,Department d
where e.DepartmentId=d.id)as a
where rk<=3

 

---

 注意count(*) over(partition by的这种用法，以后可以有意识的用一下。

 

select employee_id, count(*) over(partition by team_id) team_size
from employee

 或者

select employee_id,team_size from Employee E left join 
(select team_id,count(*) team_size from Employee
group by team_id) a 
on E.team_id=a.team_id