mysql实现rownum()over_mysql--实现oracle的row_number() over功能

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

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

select @rownum:=@rownum+1rownum,a.objectno,a.phaseno,if(@objno=a.OBJECTNO or (@objno is null and a.objectno is null),@rank:=@rank+1,@rank:=1) asrow_number,@objno:=a.OBJECTNOfrom(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,'')) phasenofrom(SELECT * from flow_task order by OBJECTNO,phaseno asc)aGROUP 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_phasenofrom(SELECT * from flow_task order by OBJECTNO,phaseno asc)aGROUP BY a.objectno

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

rank()实现：

select @rownum:=@rownum+1rownum,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)asrow_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)asskip,@objno:=a.OBJECTNO,@phaseno:=a.phasenofrom(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+1rownum,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)asrow_number,@objno:=a.OBJECTNO,@phaseno:=a.phasenofrom(SELECT * from flow_task order by OBJECTNO,phaseno asc)a,

(select @rownum :=0,@objno:=null,@phaseno:=null,@rank:=1)b