菜单

MySQL达成SQL Server排行函数

2019年4月19日 - sqlite

近来在MySQL中蒙受分组排序查询时,突然发现MySQL中从不row_number()
over(partition by colname)那样的分组排序。
而且鉴于MySQL中从不类似于SQL
Server中的row_number()、rank()、dense_rank()等排行函数,全部找到以下降成格局,在此轻巧记录一下。

怎么是排行函数?说实话笔者也不甚精通,笔者知道 order by
是排序用的,那么什么样又是排名函数呢?

 

接下去看多少个示范就知晓了。

率先创设1个表并插入测试数据。

先是建立一个表,随便插入1些数量。

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_NUMBE中华V函数:直接排序,ROW_NUMBEPRADO函数是上述升举行间接排序,并且以接二连三的逐一给每壹行数据3个唯一的序号。(即排行一而再)

图片 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
子句中钦定的列,假使回到1行数据与另1行具备同等的值,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;

在排行的经过中,保持四个之中计数值,当班值日有所变动时,排行序号将有3个跳跃。(即排行不一连)

结果如下:

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

图片 4

图片 5

 

能够分明的旁观有四行数据并列第3名,然后径直正是第4名,那是因为 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行数据并列第1名,不过接下去依旧是第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
个组获得三个附加行。因而,恐怕不会怀有组都获得相当于数量的行,然则组大小最大只可能离开1行。

结果如下:

例如,就算总行数是 五三,组数是 五,5叁 / 5也便是10余数是3,按上边个规则就是,每组分配10行,又因余数为3,所从前边三组每组附加壹行。

图片 8

则前多少个组每组包涵 11 行,别的五个组每组包蕴 10 行。

 

单向,假诺总行数可被组数整除,则行数就要组之间平均分布。
例如,要是总行数为 50,有多少个组,则每组将富含 十 行。

实现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/三等于三余数一。

图片 10

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

 

 

完成分组聚合字符串,即把钦点列的值拼成字符串。
在SQL Server中时选用了中等变量实现,以往在MySQL中就相比简单了。
MySQL提供了3个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属于排行函数,OVECR-V()正是窗口函数。

结果如下:

窗口函数OVELX570()钦赐一组行,开窗函数总结从窗口函数输出的结果集中各行的值。

图片 11

开窗函数不须要选择GROUP
BY就足以对数据开始展览分组,还足以而且再次来到基础行的列和聚合列。

 

排行开窗函数能够独立接纳OPRADODEPAJERO 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猎豹CS6 BY 钦赐排行开窗函数的逐条。在排行开窗函数中必须接纳O奥迪Q7DEPRADO 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_NUMBECRUISER再在每壹组中张开延续排序。

 

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 那1列有4种不相同的值,所以1律是分为肆组,然后 RANK
再在每1组中实行排序,因为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 这1列有肆种不一样的值,所以1律是分为肆组,然后
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
那1列有四种分裂的值,所以一律是分为四组。第2组有壹条数据,所以就一个区。第叁组有四条数据,4/叁约等于壹余数一,所以第三组分为二个区,又因余数为壹,所以第贰个区附加一行。第二组有三条数据,3/3约等于一余数为0,所以第三组有叁个区。第四组有贰条数据,所以分为3个区。

PS:在排序开窗函数中选取 PARTITION BY
子句要求停放在 O奔驰G级DECR-V BY子句从前。

 

参考:

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

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图