SQL Server排名函数与排名开窗函数

发布时间:2019-08-31  栏目:SQL  评论:0 Comments

近年在MySQL中境遇分组排序查询时,突然发掘MySQL中从未row_number()
over(partition by colname)那样的分组排序。
与此同不日常间由于MySQL中从不临近于SQL
Server中的row_number()、rank()、dense_rank()等排行函数,全部找到以下实现格局,在此轻易记录一下。

怎么样是排名函数?说实话作者也不甚清楚,小编清楚 order by
是排序用的,那么哪些又是排行函数呢?

 

接下去看多少个示范就明白了。

首先成立三个表并插入测量试验数据。

先是创立三个表,随意插入一些数码。

create table demo.Student (
   ID int(11) NOT NULL AUTO_INCREMENT,
   StuNo varchar(32) NOT NULL,
   StuName varchar(10) NOT NULL,
   StuAge int(11) DEFAULT NULL,
   PRIMARY KEY (ID)
 )
 engine=innodb auto_increment=1 default charset=utf8 collate=utf8_general_ci;

insert into demo.Student(StuNo,StuName,StuAge) values('A001','小明',22);
insert into demo.Student(StuNo,StuName,StuAge) values('A005','小李',23);
insert into demo.Student(StuNo,StuName,StuAge) values('A007','小红',24);
insert into demo.Student(StuNo,StuName,StuAge) values('A003','小明',22);
insert into demo.Student(StuNo,StuName,StuAge) values('A002','小李',23);
insert into demo.Student(StuNo,StuName,StuAge) values('A004','小红',24);
insert into demo.Student(StuNo,StuName,StuAge) values('A006','小王',25);

select * from demo.Student;

图片 1

测验数据如下:

ROW_NUMBEEscort函数:直接排序,ROW_NUMBE纳瓦拉函数是上述升举办直接排序,何况以接二连三的依次给每一行数据三个独一的序号。(即排名接二连三)

图片 2

1 -- 以下是根据 U_Pwd 这一列进行排名(升序)
2 select *,
3 '第'+convert(varchar,ROW_NUMBER() over(order by U_Pwd))+'名' RowNum
4 from UserInfo

 

图片 3

实现row_number()排名函数,按学号(StuNo)排序。

RANK 函数:并列排序,在 order by
子句中内定的列,假使回去一行数据与另一行具备同等的值,rank函数将给那几个行赋予一样的排名数值。

-- @row_number:=0,设置变量@row_number的初始值为0。
-- @row_number:=@row_number+1,累加@row_number的值。
select ID,StuNo,StuName,StuAge,@row_number:=@row_number+1 as row_number 
from demo.Student a,
(
    select @row_number:=0
) b
order by StuNo asc;

在排行的进度中,保持叁个里边计数值,当班值日有所退换时,排行序号将有多少个踊跃。(即排名不三回九转)

结果如下:

1 -- 以下是根据 U_Pwd 这一列进行排名(升序)
2 select *,
3 '第 '+convert(varchar,rank() over(order by U_Pwd))+' 名' RowNum
4 from UserInfo

图片 4

图片 5

 

可以一览无余的旁观有4行数据并列第2名,然后直接就是第6名,那是因为 order by
子句中钦定的列 U_Pwd 的值同样。

兑现rank()排行函数,按学生年龄(StuAge)排序。

DENSE_RANK
函数:并列排序,
那或多或少与 RANK() 函数类似,order by
子句钦定的列的值同样,排行数值同样,然则前面是接连的。(即排名接二连三)

-- @StuAge:=null,设置变量@StuAge的初始值为null
-- @rank:=0,设置变量@rank的初始值为0
-- @inRank:=1,设置变量@inRank的初始值为1
-- if(@StuAge=StuAge,@rank,@rank:=@inRank),指定排序列的值不变时,@rank的值不变;指定排序列的值变化时,@rank的值跳变为@inRank内部计数的值
-- @inRank:=@inRank+1,每一行自增1,用于实现内部计数
select t.ID,t.StuNo,t.StuName,t.StuAge,t.row_rank 
from 
(
    select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@rank,@rank:=@inRank) as row_rank,@inRank:=@inRank+1,@StuAge:=StuAge 
    from demo.Student a,
    (
        select @StuAge:=null,@rank:=0,@inRank:=1 
    ) b 
    order by StuAge asc 
) t;
1 -- 以下是根据 U_Pwd 这一列进行排名(升序)
2 select *,
3 '第 '+convert(varchar,DENSE_RANK() over(order by U_Pwd))+' 名' RowNum
4 from UserInfo

结果如下:

图片 6

图片 7

能够看来便是有4行数据并列第2名,可是接下去照旧是第3名。

 

NTILE
函数:
将查询的结果分发到钦命数量的组中。
各样组有编号,编号从1发轫。 对于每一行,NTILE 将再次回到此行所属的组的号子。

实现dense_rank()排行函数,按学生年龄(StuAge)排序。

组中的行数计算方法为 total_num_rows(结果集的总公司数) /
num_groups(钦赐的组数)。

-- @StuAge:=null,设置变量@StuAge的初始值为null
-- @rank:=0,设置变量@rank的初始值为0
-- if(@StuAge=StuAge,@rank,@rank:=@rank+1),指定排序列的值不变时,@rank的值不变;指定排序列的值变化时,@rank的值自增1
select t.ID,t.StuNo,t.StuName,t.StuAge,t.row_rank 
from 
(
    select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@rank,@rank:=@rank+1) as row_rank,@StuAge:=StuAge
    from demo.Student a,
    (
        select @StuAge:=null,@rank:=0 
    ) b 
    order by StuAge asc 
) t;

若是有余数 n,则后边 n
个组得到一个附加行。由此,大概不会持有组都得到相当于数量的行,不过组大小最大只只怕离开一行。

结果如下:

例如,如若总行数是 53,组数是 5,53 / 5
对等10余数是3,按上边个准则就是,每组分配10行,又因余数为3,所从前边3组每组附加一行。

图片 8

则前五个组每组包罗 11 行,其他五个组每组包蕴 10 行。

 

一派,如果总行数可被组数整除,则行数将要组之间平均遍布。
举例,若是总行数为 50,有四个组,则每组将包罗 10 行。

实现row_number() over(partition by colname order by
colname)分组排行函数,按学生年龄(StuAge)分组排序。

1 -- 以下是根据 U_Pwd 这一列进行分组
2 select *,
3 '第 '+convert(varchar,NTILE(3) over(order by U_Pwd))+' 组' RowNum
4 from UserInfo
-- @StuAge:=null,设置变量@StuAge的初始值为null
-- @row_number:=0,设置变量@row_number的初始值为0
-- if(@StuAge=StuAge,@row_number:=@row_number+1,@row_number:=1),指定排序列的值不变时,@row_number的值自增1;指定排序列的值变化时,@row_number的值等于1
select t.ID,t.StuNo,t.StuName,t.StuAge,t.row_number 
from 
(
    select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@row_number:=@row_number+1,@row_number:=1) as row_number,@StuAge:=StuAge 
    from demo.Student a,
    (
        select @StuAge:=null,@row_number:=0 
    ) b 
    order by StuAge asc 
) t;

图片 9

结果如下:

本条表中有10条数据,钦点分为3组,10/3等于3余数1。

图片 10

PS:排行函数前面总得有 over() 子句。

 

 

贯彻分组聚合字符串,即把内定列的值拼成字符串。
在SQL Server中时使用了中等变量完毕,未来在MySQL中就比较轻巧了。
MySQL提供了一个group_concat()函数,能够把钦点列的值拼成八个字符串,并得以按钦点排序形式拼成字符,之间用逗号隔绝。如下示例:

排名开窗函数:

select group_concat(StuNo order by StuNo asc) as column1,group_concat(StuNo order by ID asc) as column2 
from demo.Student 

ROW_NUMBER、DENSE_RANK、RANK、NTILE属于排行函数,OVELAND()正是窗口函数。

结果如下:

窗口函数OVE昂科拉()钦定一组行,开窗函数总结从窗口函数输出的结果聚集各行的值。

图片 11

开窗函数不须求选拔GROUP
BY就能够对数码实行分组,还是能而且再次回到基础行的列和聚合列。

 

排名开窗函数能够单独行使OOdysseyDE昂科拉 BY 语句,也足以和PARTITION BY同期接纳。

select StuName,group_concat(StuNo order by StuNo asc) as column1,concat('"',group_concat(StuNo order by StuNo asc),'"') as column2 
from demo.Student 
group by StuName 
order by StuAge 

ODE锐界 BY 钦命排行开窗函数的各种。在排行开窗函数中必需接纳OCR-VDE智跑 BY语句。

结果如下:

PARTITION BY用于将结果集举办分组,开窗函数应用于每一组。

图片 12

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Pwd 排序
2 select *,
3 '第'+convert(varchar,ROW_NUMBER() over(partition by U_Pwd order by U_Pwd))+'名' RowNum
4 from UserInfo

 

图片 13

因为 U_Pwd 这一列有4种不一致的值,所以分为4组,然后 ROW_NUMBE揽胜极光再在每一组中实行一连排序。

 

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Pwd 排序
2 select *,
3 '第'+convert(varchar,rank() over(partition by U_Pwd order by U_Pwd))+'名' RowNum
4 from UserInfo

图片 14

因为 U_Pwd 这一列有4种差别的值,所以同样是分为4组,然后 RANK
再在每一组中实行排序,因为RANK是同仁一视排序,所以全是第一名。上面换个字段排序试试看。

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Name 排序
2 select *,
3 '第'+convert(varchar,rank() over(partition by U_Pwd order by U_Name))+'名' RowNum
4 from UserInfo

图片 15

 

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Pwd 排序
2 select *,
3 '第'+convert(varchar,DENSE_RANK() over(partition by U_Pwd order by U_Pwd))+'名' RowNum
4 from UserInfo

图片 16

因为 U_Pwd 这一列有4种差别的值,所以一律是分为4组,然后
DENSE_RANK 再在每一组中举行排序,因为DENSE_RANK也是同等对待排序,所以全部是头名。上面换个字段排序试试看。

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Name 排序
2 select *,
3 '第'+convert(varchar,DENSE_RANK() over(partition by U_Pwd order by U_Name))+'名' RowNum
4 from UserInfo

图片 17

 

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 NTILE(3) 指定的组数分组,最后在根据 order by 子句指定的字段 U_Pwd 排序 
2 select *,
3 '第'+convert(varchar,NTILE(3) over(partition by U_Pwd order by U_Pwd))+'名' RowNum
4 from UserInfo

图片 18

因为 U_Pwd
这一列有4种分歧的值,所以相同是分为4组。第1组有1条数据,所以就1个区。第2组有4条数据,4/3等于1余数1,所以第2组分为3个区,又因余数为1,所以第2个区附加1行。第3组有3条数据,3/3对等1余数为0,所以第3组有3个区。第4组有2条数据,所以分为2个区。

PS:在排序开窗函数中动用 PARTITION BY
子句须要停放在 O冠道DE本田CR-V BY子句从前。

 

参考:

http://www.cnblogs.com/jhxk/articles/2531595.html

留下评论

网站地图xml地图