常用sql001_partition by以及row_number()和 dense_rank()和rank()区别
常用sql001_partition by以及row_number()和 dense_rank()和rank()区别
[sql] create table student ( sid varchar2(10), --学号 sname varchar2(10), --姓名 classid varchar2(10), --班级号 score int --分数 ); 问题一: 班级中平均分最高的 班级号 以及 分数; [sql] 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] --求解sql: SELECT * FROM (SELECT CLASSID,AVG(score) score_ FROM STUDENT GROUP BY CLASSID ORDER BY score_ DESC) WHERE ROWNUM=1 ;
问题二: 求每个班级第二名的学生的 学号 和 分数 (如果并列第二,全部求出)
原始数据:
执行如下sql:
[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:
[sql] select t.* , dense_rank() over(partition by classid order by score) as cn from student t
以上sql的执行结果:
以下是 row_number() 和 dense_rank的区别: 区别在于最后一行 cn 上
[sql] select t.* , row_number() over(partition by classid order by score) as cn from student t
rank() 的用法:
[sql] select t.* , RANK() over(partition by classid order by score) as cn from student t