开窗函数测试

数据1：各个班级人数

select dept_id,count(id) from employee group by dept_id;

问题1：查询出人数最多的3个部门，包含部门信息，人员数量。

select * from 
( select dept_id, row_number() over(order by count(id) desc) num
 from employee group by dept_id ) a
where a.num <=3;

测试1：按班级人数从多到少排名

select dept_id,count(id),row_number() over(order by count(id) desc) num
from employee group by dept_id;

测试2：按班级人数从多到少排名（排名不连续）

select dept_id,count(id),rank() over(order by count(id) desc) num
from employee group by dept_id;

测试3：按班级人数从多到少排名（排名连续）

select dept_id,count(id),dense_rank() over(order by count(id) desc) num
from employee group by dept_id;