MySQL实现SQL Server排名函数

图片 3

最近在MySQL中遇到分组排序查询时,突然发现MySQL中没有row_number()
over(partition by colname)这样的分组排序。
并且由于MySQL中没有类似于SQL
Server中的row_number()、rank()、dense_rank()等排名函数,所有找到以下实现方法,在此简单记录一下。

over()分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。
例子:

 

select *from (select name,class,score,rank() over(partition by class order by score desc) mm from t2 ) where mm=1

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

通过class班级进行分组,并根据score分数进行排序,用rank()函数排序方法为mm列赋予序号,然后mm=1就可以找到每组的第一名,当然可以根据score就行倒序可以找到最后一名。

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;

row_number() over(partition by ... order by ...)

测试数据如下:

简单的说row_number()从1开始,为每一条分组记录返回一个数字,
row_number() over(order by score desc)是先把score
列降序,再为降序以后的没条xlh记录返回一个序号。(如果没有分组可以理解成将整个结果作为一个分组)

图片 1

row_number() over(partition by class order by score
desc)表示根据class分组,在分组内部根据 score
排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

 

rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)

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

作为分数函数中有关排序的rank(),dense_rank(),row_number()。

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

rank()
over是的作用是查出指定条件后进行一个排名,但是有一个特点。假如是对学生排名,那么实用这个函数,成绩相同的两名是并列(名次为1,2,2,4)

结果如下:

dense_rank()的作用和rank()很像,唯一区别就是,相同成绩并列以后,下一位同学并不空出并列所占的名次(名次为1,2,2,3)

图片 2

row_number()就不一样了,它和上面两种的区别就很明显了,这个函数不需要考虑是否并列,哪怕根据条件查询出来的数值相同也会进行连续排名。

 

对于多表查询,可以为空置加上一个判断来显示查询数据为空的数据。

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

case when score is null then 1 else rank() over (partition by class order by score desc ) end as mm

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

其他常用的分析函数:

结果如下:

count() over(partition by ... order by ...) max() over(partition by ... order by ...) min() over(partition by ... order by ...) sum() over(partition by ... order by ...) avg() over(partition by ... order by ...) first_value() over(partition by ... order by ...) last_value() over(partition by ... order by ...) lag() over(partition by ... order by ...) lead() over(partition by ... order by ...)

图片 3

 

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

发表评论

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