欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

sqlserver的over开窗函数(与排名函数或聚合函数一起使用)

程序员文章站 2024-01-16 16:06:23
首先初始化表和数据 一:首先我们先举个例子来认识一下over的庐山真面目 现在我们的需求是查询出来两个班级的前三名可以通过以下: 之前我的想法是根据分数排序然后取三条,后面发现如果分数一致的话,比如有3个人并列第一名,则这样的写法就完全不满足需求,所以我们可以通过over开窗函数来实现上面的要求 得 ......

首先初始化表和数据

 1 create table t_student(
 2   id int,
 3   name varchar(100),
 4   score int,
 5   classid int
 6 );
 7 
 8 insert into t_student values (1,'a',75,1);
 9 insert into t_student values (2,'b',78,2);
10 insert into t_student values (3,'c',74,1);
11 insert into t_student values (4,'d',85,2);
12 insert into t_student values (5,'e',80,1);
13 insert into t_student values (6,'f',82,2);
14 insert into t_student values (7,'g',98,1);
15 insert into t_student values (8,'h',90,2);
16 insert into t_student values (9,'i',90,2);
17 
18 ---班级表
19 create table  t_class(
20   id int,
21   name nvarchar(100)
22 );
23 
24 insert into t_class values (1,'一班');
25 insert into t_class values (2,'二班');
26  

 

一:首先我们先举个例子来认识一下over的庐山真面目

现在我们的需求是查询出来两个班级的前三名可以通过以下:

之前我的想法是根据分数排序然后取三条,后面发现如果分数一致的话,比如有3个人并列第一名,则这样的写法就完全不满足需求,所以我们可以通过over开窗函数来实现上面的要求

select  * from ( select name ,score ,classid ,rank() over ( partition by classid order by score desc ) scorerank from t_student) as s
where   s.scorerank < 4;

得到的结果如下:

sqlserver的over开窗函数(与排名函数或聚合函数一起使用)

注意:

1:sqlserver中的from (字表)的时候一定要as,否则报语法错误

2:rank() 这个指的是为每一组的行生成一个序号,与row_number()不同的是如果按照order by的排序,如果有相同的值会生成相同的序号,并且接下来的序号是不连序的。例如两个相同的行生成序号3,那么接下来会生成序号5。

3:rank() over(partition by classid order by score desc )是指的先根据classid分组,然后再根据score分数倒叙排列,则是指的分组后生成rank的序列化号

单单执行 select name ,score ,classid ,rank() over ( partition by classid order by score desc ) scorerank from t_student会出现下面的结果:

sqlserver的over开窗函数(与排名函数或聚合函数一起使用)

 

二:over的一些语法与用法

语法结构:over( [ partition by ... ] [ order by ... ] )

1 、partition by 字段名字a:子句进行分组,partition by是固定的分组语法;

2、order by 字段名字b:子句进行排序,order by 是固定的排序语法。

比如我们上面的例子就是用到了partition by classid 和 order by score这样的用法了,注意:如果联合使用指的意思是:先分组然后再排序

over()函数不能单独使用,必须跟在 排名函数( row_number、dense_rank、rank、ntile) 或 5种聚合函数(sum、max、min、avg、count)后边。

 

三:排名开窗函数

语法结构:排名函数() over ( [ <partition_by字段> ] <order_by字段> )

注意:在排名开窗函数中必须使用order by语句

 sqlserver的over开窗函数(与排名函数或聚合函数一起使用)

下面分别介绍一下各个排名函数的用法和效果

1、row_number():为每一组的行记录按顺序生成一个唯一的行号。这个用的最多的是不连续的id上下分页,重新生成id,也就是一行会生成一个连续的id值,如下:

sqlserver的over开窗函数(与排名函数或聚合函数一起使用)

注意:如果是分组,则每个组里面的id是连续的

 

2、rank()也为每一组的行生成一个序号,与row_number()不同的是如果按照order by的排序,如果有相同的值会生成相同的序号,并且接下来的序号是不连序的。例如两个相同的行生成序号3,那么接下来会生成序号5。

sqlserver的over开窗函数(与排名函数或聚合函数一起使用)

 

3、dense_rank()和rank()类似,不同的是如果有相同的序号,那么接下来的序号不会间断。也就是说如果两个相同的行生成序号3,那么接下来生成的序号还是4。

sqlserver的over开窗函数(与排名函数或聚合函数一起使用)

 

4、ntile (integer_expression) 按照指定的数目将数据进行分组,并为每一组生成一个序号。

 sqlserver的over开窗函数(与排名函数或聚合函数一起使用)

 

四:聚合开窗函数

语法结构:聚合函数( ) over ( [ partition by 字段] [order by 字段]) ,其中【partition by 字段】和【order by 字段】是可选择的

1:max聚合函数

sqlserver的over开窗函数(与排名函数或聚合函数一起使用)

 

2:sum聚合函数

sqlserver的over开窗函数(与排名函数或聚合函数一起使用)

 

3:count聚合函数

sqlserver的over开窗函数(与排名函数或聚合函数一起使用)

下面的min和avg都是类似,暂时不举例了!

 另外开窗函数和聚合函数的不同之处是:开窗函数对于每个组返回多行,而聚合函数对于每个组只返回一行