mysql 排名函数_MySQL实现SQL Server排名函数

最近在MySQL中遇到分组排序查询时，突然发现MySQL中没有row_number() over(partition by colname)这样的分组排序。

并且由于MySQL中没有类似于SQL Server中的row_number()、rank()、dense_rank()等排名函数，所有找到以下实现方法，在此简单记录一下。

首先创建一个表并插入测试数据。

create tabledemo.Student (

IDint(11) NOT NULLAUTO_INCREMENT,

StuNovarchar(32) NOT NULL,

StuNamevarchar(10) NOT NULL,

StuAgeint(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;

测试数据如下：

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

--@row_number:=0，设置变量@row_number的初始值为0。--@row_number:=@row_number+1，累加@row_number的值。

select ID,StuNo,StuName,StuAge,@row_number:=@row_number+1 asrow_numberfromdemo.Student a,

(select @row_number:=0) border by StuNo asc;

结果如下：

实现rank()排名函数，按学生年龄(StuAge)排序。

--@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，用于实现内部计数

selectt.ID,t.StuNo,t.StuName,t.StuAge,t.row_rankfrom(select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@rank,@rank:=@inRank) as row_rank,@inRank:=@inRank+1,@StuAge:=StuAgefromdemo.Student a,

(select @StuAge:=null,@rank:=0,@inRank:=1) border by StuAge asc) t;

结果如下：

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

--@StuAge:=null，设置变量@StuAge的初始值为null--@rank:=0，设置变量@rank的初始值为0--if(@StuAge=StuAge,@rank,@rank:=@rank+1)，指定排序列的值不变时，@rank的值不变；指定排序列的值变化时，@rank的值自增1

selectt.ID,t.StuNo,t.StuName,t.StuAge,t.row_rankfrom(select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@rank,@rank:=@rank+1) as row_rank,@StuAge:=StuAgefromdemo.Student a,

(select @StuAge:=null,@rank:=0) border by StuAge asc) t;

结果如下：

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

--@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

selectt.ID,t.StuNo,t.StuName,t.StuAge,t.row_numberfrom(select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@row_number:=@row_number+1,@row_number:=1) as row_number,@StuAge:=StuAgefromdemo.Student a,

(select @StuAge:=null,@row_number:=0) border by StuAge asc) t;

结果如下：

实现分组聚合字符串，即把指定列的值拼成字符串。

在SQL Server中时利用了中间变量实现，现在在MySQL中就比较简单了。

MySQL提供了一个group_concat()函数，可以把指定列的值拼成一个字符串，并可以按指定排序方式拼成字符，之间用逗号隔开。如下示例：

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

结果如下：

select StuName,group_concat(StuNo order by StuNo asc) as column1,concat('"',group_concat(StuNo order by StuNo asc),'"') ascolumn2fromdemo.Studentgroup byStuNameorder by StuAge

结果如下：