sql例子如下

正常业务查询根据 创建时间排序查询

SELECT * FROM SHWZ_JHSB_THREEON_PROJECT WHERE PROJECTTAG = '三上' ORDER BY CREATETIME DESC;

执行查询结果如下图

图1png

查询第一页 不加rownum排序的分页查询

--SELECT * FROM (SELECT * FROM SHWZ_JHSB_THREEON_PROJECT WHERE PROJECTTAG = '三上' ORDER BY CREATETIME DESC) WHERE ROWNUM <= 15;

执行查询结果如下图、排序结果与图1的顺序不一致

图2.png

查询第一页 加rownum排序的分页查询

select *  from (select t.*,rownum  from  (SELECT * FROM SHWZ_JHSB_THREEON_PROJECT WHERE PROJECTTAG = '三上' ORDER BY CREATETIME DESC) t  order by rownum) where rownum < 15 ;

执行查询结果如下图、排序结果与图1的顺序一致

图3.png

查询多页 不加rownum排序的分页查询

SELECT * FROM ( SELECT ROW_.*, ROWNUM ROWNUM_ FROM (SELECT * FROM SHWZ_JHSB_THREEON_PROJECT WHERE PROJECTTAG = '三上' ORDER BY CREATETIME DESC) ROW_ WHERE ROWNUM <= 30) WHERE ROWNUM_ > 15

执行查询结果如下图、排序结果与图1的顺序不一致

图4.png

查询多页 加rownum排序的分页查询

SELECT * FROM ( SELECT ROW_.*, ROWNUM ROWNUM_ FROM (select ROW__.*,rownum  from  (SELECT * FROM SHWZ_JHSB_THREEON_PROJECT WHERE PROJECTTAG = '三上' ORDER BY CREATETIME DESC) ROW__  order by rownum) ROW_ WHERE ROWNUM <= 30) WHERE ROWNUM_ > 0

执行查询结果如下图、排序结果与图1的顺序一致

图5.png

总结

有指定排序的字段，需要在sql外面套一层 根据rownum 的排序查询，再进入分页的查询