【SQL】order by 分页排序

遇到项目，需要按照结束时间从最近的开始排序，首先思路就是先排序，再分页，分页是按照每页10行，也就是第一页查询行号1-10的数据来实现；

1.先查询数据并排序

        select row_number() over(order by ROWNUM) as rn,
               to_char(bp.startdate, 'yyyy-mm-dd HH:mi:ss') "startdate",
               to_char(bp.enddate, 'yyyy-mm-dd HH:mi:ss') "enddate"
          from T_Client_Policy cp
          left join T_Bus_Policy bp
            on cp.policyno = bp.policyno
           and cp.riskcode = bp.riskcode
         where cp.clientno = '200054382009' order by enddate desc

查询后排序正常

2.输出行号为1-10的数据

select *
  from (select row_number() over(order by ROWNUM) as rn,
               to_char(bp.startdate, 'yyyy-mm-dd HH:mi:ss') "startdate",
               to_char(bp.enddate, 'yyyy-mm-dd HH:mi:ss') "enddate"
          from T_Client_Policy cp
          left join T_Bus_Policy bp
            on cp.policyno = bp.policyno
           and cp.riskcode = bp.riskcode
         where cp.clientno = '200054382009' order by enddate desc)
 where rn between 1 and 10

发现数据并的结束日期并没有按照2019年为最开始往下排序

查看原因，发现结束日期并不是我们想象中的2019排在最前边，仔细看看，是定义的行号不对，

原因：SQL在进行查询的时候，先查询后排序，所以，我们需要在外边加一层已经排序好的sql，对最外层进行行号取1-10行为一进行分页

正确：

select *
  from (select row_number() over(order by ROWNUM) as rn, startdate, enddate
          from (select to_char(bp.startdate, 'yyyy-mm-dd HH:mi:ss') startdate,
                       to_char(bp.enddate, 'yyyy-mm-dd HH:mi:ss') enddate
                  from T_Client_Policy cp
                  left join T_Bus_Policy bp
                    on cp.policyno = bp.policyno
                   and cp.riskcode = bp.riskcode
                 where cp.clientno = '200054382009'
                 order by enddate desc))
 where rn between 1 and 10