分区函数Partition By的用法

刺骨的言语ヽ痛彻心扉 2022-02-28 00:14 282阅读 0赞

group by是分组函数，partition by是分区函数（像sum()等是聚合函数），注意区分。

### 1、over函数的写法： ###

over（partition by cno order by degree ）

先对cno 中相同的进行分区，在cno 中相同的情况下对degree 进行排序

### 2、分区函数Partition By与rank()的用法“对比”分区函数Partition By与row\_number()的用法 ###

例：查询每名课程的第一名的成绩

###### （1）使用rank() ######

SELECT	* 
    FROM	(select sno,cno,degree,
          	rank()over(partition by cno order by degree desc) mm 
          	from score) 
    where mm = 1;

得到结果：  
![在这里插入图片描述][20190323180421725.png]

###### （2）使用row\_number() ######

SELECT * 
    FROM   (select sno,cno,degree,
           row_number()over(partition by cno order by degree desc) mm 
           from score) 
    where mm = 1;

得到结果：  
![在这里插入图片描述][20190323180844735.png]

###### （3）rank()与row\_number()的区别 ######

由以上的例子得出，在求第一名成绩的时候，不能用row\_number()，因为如果同班有两个并列第一，row\_number()只返回一个结果。

### 2、分区函数Partition By与rank()的用法“对比”分区函数Partition By与dense\_rank()的用法 ###

例：查询课程号为‘3-245’的成绩与排名

###### （1） 使用rank() ######

SELECT * 
    FROM   (select sno,cno,degree,
           rank()over(partition by cno order by degree desc) mm 
           from score) 
    where cno = '3-245'

得到结果：  
![在这里插入图片描述][20190325162112385.png]

###### （2） 使用dense\_rank() ######

SELECT * 
    FROM   (select sno,cno,degree,
           dense_rank()over(partition by cno order by degree desc) mm 
           from score) 
    where cno = '3-245'

得到结果：  
![在这里插入图片描述][20190325162956912.png]

###### （3）rank()与dense\_rank()的区别 ######

由以上的例子得出，rank()和dense\_rank()都可以将并列第一名的都查找出来；但rank()是跳跃排序，有两个第一名时接下来是第三名；而dense\_rank()是非跳跃排序，有两个第一名时接下来是第二名。

[20190323180421725.png]: /images/20220228/cf882ba1f7e149adb47015eb689f60fd.png
[20190323180844735.png]: /images/20220228/ab7a0f9b740943db8e9c895e31060e52.png
[20190325162112385.png]: /images/20220228/522ba1c327424646b320e61159b10340.png
[20190325162956912.png]: /images/20220228/acd7718bb6f349ae82ef376684b8d240.png