mysql--實現oracle的row_number() over功能

mysql--實現oracle的row_number() over功能

有時候我們想要得到每個分組的前幾條記錄，這個時候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

推薦閱讀：