oracle rownum order by 爬坑

心已赠人 2023-06-11 03:25 21阅读 0赞

系统中同时使用mysql和oracle数据库，需要保证两边的兼容性。今天测试的同学发现一个oracle版本的bug，经过排查发现是rownum和order by引起的。

原sql如下：

select ACT_NAME_                               "actName",
                   TO_CHAR(END_TIME_, 'yyyy-mm-dd hh24:mi:ss') "endTime"
            from act_hi_actinst
            where ACT_TYPE_ = 'userTask'
              and PROC_INST_ID_ in
                  (select t.PROC_INST_ID_
                   from (select *
                         from act_hi_procinst
                         where BUSINESS_KEY_ = concat('mkt_cam_audit', #{campaignId})
                           and ROWNUM = 1
                         order by START_TIME_ desc
                        ) t)
            order by  (case when "endTime" is  null then 1 when "endTime" is not null then 0 end), "endTime"

主要问题在子查询里边，子查询大致逻辑是根据时间排序后（将END\_TIME\_为空的行排在最前面），取出第一行数据，然而在使用条件：

where BUSINESS_KEY_ = concat('mkt_cam_audit', #{campaignId})
                           and ROWNUM = 1

始终得到的不是首行数据。

最后发现问题在于：

**oracle中rownum和order by同时使用时，需要将order by放在里层，而rownum=1放在外层**，也就是先排序号，再取第一条数据，这和mysql是不一样的，mysql则没有这么麻烦。

修改后sql如下：

select ACT_NAME_                               "actName",
               TO_CHAR(END_TIME_, 'yyyy-mm-dd hh24:mi:ss') "endTime"
        from act_hi_actinst
        where ACT_TYPE_ = 'userTask'
          and PROC_INST_ID_ in
              (select *
               from (select t.PROC_INST_ID_ from (
                                       select *
                                       from act_hi_procinst
                                       where BUSINESS_KEY_ = concat('mkt_cam_audit', #{campaignId})
                                         
                                       order by START_TIME_ desc
                                   ) t
                                       ) where ROWNUM = 1)
        order by  (case when "endTime" is  null then 1 when "endTime" is not null then 0 end), "endTime"

--------------------

All efforts, only for myself, no longer for others