常用sql001_partition by 以及 row_number()和 dense_rank()和rank()区别

create table  student (
 sid varchar2(10), --学号
 sname varchar2(10), --姓名
 classid varchar2(10), --班级号
 score  int   --分数
);

问题一：   班级中平均分最高的 班级号 以及 分数；

 

insert into  student values('001','z001','1',80);
insert into  student values('002','z001','1',90);
insert into  student values('003','z001','1',70);
insert into  student values('004','z001','1',90);
insert into  student values('005','z001','1',80);
insert into  student values('006','z001','1',70);

insert into  student values('007','z001','2',60);
insert into  student values('008','z001','2',70);
insert into  student values('009','z001','2',50);
insert into  student values('010','z001','2',70);
insert into  student values('011','z001','2',60);
insert into  student values('012','z001','2',50);

 

--求解sql：

 SELECT * FROM (SELECT  CLASSID,AVG(score) score_ FROM STUDENT GROUP BY CLASSID  ORDER BY score_ DESC)
 WHERE ROWNUM=1 ;
 

问题二： 求每个班级第二名的学生的  学号 和  分数 （如果并列第二，全部求出）

 

原始数据：

执行如下sql：

 

select  s.* from (
select  t.* , dense_rank() over(partition by classid order by score) as cn  from  student   t ) s where s.cn = 2;

 

执行结果：

分析以上sql：

 

select  t.* , dense_rank() over(partition by classid order by score) as cn  from  student   t

 

以上sql的执行结果：

以下是   row_number()  和  dense_rank的区别： 区别在于最后一行 cn 上

 

select  t.* , row_number() over(partition by classid order by score) as cn  from  student   t

 

rank() 的用法：

select  t.* , RANK() over(partition by classid order by score) as cn  from  student   t