以前用的很好的DB2分页程序,突然出现结果错乱,第一页中的内容,在第二页中也出现,
检查原因,发现是因为Order By 的字段有重复的值引起的(Oracle下同样会有这个问题)。
比如 Order By sno ,如果sno字段的值有重复的,就会出现这个问题。
以前的分页语句:
select * from (select query__.*,rownumber() over() as rownum__ from
(select * from xxx order by xxx) fetch first 20 rows only) query__) wrapped__
where rownum__> 0
需要将order by xxx 放到over中即可:
select * from (select query__.*,rownumber() over(order by xxx) as rownum__ from
(select * from xxx order by xxx) query__) wrapped__ where rownum__ between 1 and 20