当前位置:数据库 > SQLServer >>

常用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   

 
 
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,