技术分享 数据库 MYSQL 查看内容

mysql 实现oracle的row_number() over功能

老高 | 发布于 2019-03-27 14:25| 浏览()| 评论() | 收藏() | 点赞() | 打印

摘要: 有时候我们想要得到每个分组的前几条记录,这个时候oracle中row_number函数使用非常方便,但可惜mysql没有。网上搜了些实现方法。

有时候我们想要得到每个分组的前几条记录,这个时候oracle中row_number函数使用非常方便,但可惜mysql没有。网上搜了些实现方法。

表flow_task有phaseno(序列号),objectno(编号)等几个字段,我们想实现根据编号字段分组,然后组内根据序列号排序功能

select @rownum:=@rownum+1 rownum,a.objectno,a.phaseno,
if(@objno=a.OBJECTNO or (@objno is null and a.objectno is null),
		@rank:=@rank+1,
		@rank:=1) as row_number,
@objno:=a.OBJECTNO
from(SELECT * from flow_task order by OBJECTNO,phaseno asc)a,
(select @rownum :=0,@objno:=null,@rank:=0)b

注意:order by OBJECTNO,phaseno asc 分组字段在前,排序字段在后

运行结果:

原理是,先 order by OBJECTNO,phaseno asc,这样后相同编号的记录会在一块儿,并且已经是phaseno有序asc的

select的字段一个一个的看:

@rownum:=@rownum+1,每一行在上行@rownum变量值的基础上+1

if(@objno=a.OBJECTNO or (@objno is null and a.objectno is null),@rank:=@rank+1,@rank:=1),每一行判断,当前行的objectno(编号)是否等于上一个@objno变量值,如果是在上一个@rank变量值基础上+1,否则@rank赋值1

@objno:=a.OBJECTNO,当前行objectno赋值给变量@objno

ps:如果想要分组后某个字段的几个值,也可以使用group_concat函数

select a.objectno,group_concat(ifnull(a.phaseno,'')) phaseno
from(SELECT * from flow_task order by OBJECTNO,phaseno asc)a
GROUP BY a.objectno

运行结果:

可以看到,group_concat函数把分组后某个字段的值用,拼接起来

要获取前3个值,使用substring_index函数

select a.objectno,group_concat(ifnull(a.phaseno,'')) phaseno,substring_index(group_concat(ifnull(a.phaseno,'')),',',3) sub_phaseno
from(SELECT * from flow_task order by OBJECTNO,phaseno asc)a
GROUP BY a.objectno


扩展下:怎样实现oracle中的rank() 和dense_rank()呢?我们知道rank()排序类似:1 2 2 4...,dense_rank()排序类似:1 2 2 3...

rank()实现:

select @rownum:=@rownum+1 rownum,a.objectno,a.phaseno,
if(@objno=a.OBJECTNO or (@objno is null and a.objectno is null),
		if(@phaseno=a.phaseno or (@phaseno is null and a.phaseno is null),@rank,if(@sk=0,@rank:=@rank+2,@rank:=@rank+1)),
		@rank:=1
) as row_number,
if(@objno=a.OBJECTNO or (@objno is null and a.objectno is null),
		if(@phaseno=a.phaseno or (@phaseno is null and a.phaseno is null),@sk:=0,if(@sk=0,@sk:=2,@sk:=1)),
		@sk:=1
) as skip,
@objno:=a.OBJECTNO,
@phaseno:=a.phaseno
from(SELECT * from flow_task order by OBJECTNO,phaseno asc)a,
(select @rownum :=0,@objno:=null,@phaseno:=null,@rank:=1,@sk:=1)b

dense_rank()实现:

select @rownum:=@rownum+1 rownum,a.objectno,a.phaseno,
if(@objno=a.OBJECTNO or (@objno is null and a.objectno is null),
		if(@phaseno=a.phaseno or (@phaseno is null and a.phaseno is null),@rank,@rank:=@rank+1),
		@rank:=1
) as row_number,
@objno:=a.OBJECTNO,
@phaseno:=a.phaseno
from(SELECT * from flow_task order by OBJECTNO,phaseno asc)a,
(select @rownum :=0,@objno:=null,@phaseno:=null,@rank:=1)b

发表评论(对文章涉及的知识点还有疑问,可以在这里留言,老高看到后会及时回复的。)

表情