通知
  • 关于网站更多信息请加QQ群(1061691290)
  • jpress升级到4.x,显示有些问题,修复中
  • 网站还会持续更新
文章来源于网络,无法注明出处的还请谅解,如果出处注明错误(如仍是载转),请联系我修改

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

499人浏览 / 0人评论 / | 这是对我有帮助的文章  | 分类: 数据库技术  | 标签: 数据库  | 

作者:未来的那啥

链接:https://www.cnblogs.com/yhzh/p/6222580.html

来源:博客园

有时候我们想要得到每个分组的前几条记录,这个时候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 分组字段在前,排序字段在后

运行结果:

row_number_over.jpg

原理是,先 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

运行结果:

row_number_over2.jpg

可以看到,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

row_number_over3.jpg

扩展下:怎样实现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

row_number_over4.jpg

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

row_number_over5.jpg


亲爱的读者:有时间可以点赞评论一下

点赞(0) 打赏

全部评论

还没有评论!