转自:http://www.2cto.com/database/201307/227912.html
- oracle rownum分页出现重复数据
-
oracle rownum分页出现重复数据通常一般的分页语句如下:1234567
select
*
from
(
select
row_.*, rownum rownum_
from
(
select
p.id
from
table1 p
order
by
p.DATA_UPDATE_TIME
desc
)
row_
where
rownum <= ?) b
where
b.rownum_ >?
当红字部分的 DATA_UPDATE_TIME 不能唯一确定记录的顺序就会出现这个问题,比如有重复的DATA_UPDATE_TIME 。只要将语句写成:12345678910111213select
*
from
(
select
row_.*, rownum rownum_
from
(
select
p.id
from
table1 p
order
by
p.DATA_UPDATE_TIME
desc
) row_ )
where
rownum_ > ?
and
rownum_ <= ?
就ok.另一种改法:1234567select
*
from
(
select
row_.*, rownum rownum_
from
(
select
p.id
from
table1 p
order
by
p.DATA_UPDATE_TIME
desc
,p.id
desc
)
row_
where
rownum <= ?) b
where
b.rownum_ >?
再加一个不重复的也就行了