Oracle rownum 分页注意事项

select *
  from (select temp.*, rownum r
          from (select distinct r.resource_id,
                                r.RESOURCE_OFFICIALNAME,
                                r.RESOURCE_ISRELEASE,
                                r.corp_copyright,
                                p.Pix,
                                e.editcommend_id
                  from tb_resource_info r
                  left join (select NVL(count(primarvideo_id), 0) as Pix,
                                   resource_id
                              from tb_resource_primarvideo
                             group by resource_id) p on r.resource_id =
                                                        p.resource_id
                  left join tb_editorcommend e on r.resource_id =
                                                  e.resource_id                 order by r.resource_id desc) temp
         where rownum <= 10
         order by temp.resource_id desc)
 where r > 0
 order by resource_id desc
 

与

select temp.*
  from (select distinct r.resource_id,
                        rownum r,
                        r.RESOURCE_OFFICIALNAME,
                        r.RESOURCE_ISRELEASE,
                        r.corp_copyright,
                        p.Pix,
                        e.editcommend_id
          from tb_resource_info r
          left join (select NVL(count(primarvideo_id), 0) as Pix, resource_id
                      from tb_resource_primarvideo
                     group by resource_id) p on r.resource_id =
                                                p.resource_id
          left join tb_editorcommend e on r.resource_id = e.resource_id
         order by r.resource_id desc) temp
 where r > 0
   and r <= 10
 order by temp.resource_id desc

 

两种分页有区别 第一种好。试过以后就知道原因了。