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.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+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推薦閱讀:
※索引列只要參與了計算, 查詢就會不走索引, 為什麼 MySQL 不對這種情況進行優化?
※05 實踐:SQL!!!
※SQL SERVER性能優化綜述——編碼階段(二)
※MathematicaSQL導出示例
※sql連接查詢中on篩選與where篩選的區別