最近在做一个查询功能，功能做出来以后发现分页查询时不同页码之间的数据有重复，经过分析SQL，发现错误的原因出现在排序条件上。以下为验证过程。
1、新建临时表
create table temp(
id number primary key,
name varchar2(20) unique not null,
salary number(18,2),
create_time date
);
2、向临时表中插入数据
declare
i number;
name_i varchar2(10);
salary_i number;
create_time_i date;
year_i integer;
month_i integer;
day_i integer;
hh_i integer;
mi_i integer;
ss_i integer;
begin
i := 1; --主键ID，初始值为1
while i <= 100 loop
--随机生成姓名
select dbms_random.string('a', 8) into name_i from dual;
--随机生成薪水，5000至30000
select trunc(dbms_random.value(5000, 30000), 2)
into salary_i
from dual;
if mod(i, 3) = 0 then
--三分之一的创建时间为空
create_time_i := null;
else
select trunc(dbms_random.value(2000, 2021)) into year_i from dual;
select trunc(dbms_random.value(1, 13)) into month_i from dual;
if month_i in (1, 3, 5, 7, 8, 10, 12) then
select trunc(dbms_random.value(1, 32)) into day_i from dual;
elsif month_i in (4, 6, 9, 11) then
select trunc(dbms_random.value(1, 31)) into day_i from dual;
elsif month_i = 2 and (mod(year_i, 4) = 0 or mod(year_i, 400) = 0) then
--闰年的二月份
select trunc(dbms_random.value(1, 30)) into day_i from dual;
else
select trunc(dbms_random.value(1, 29)) into day_i from dual;
end if;
select trunc(dbms_random.value(1, 24)) into hh_i from dual;
select trunc(dbms_random.value(1, 60)) into mi_i from dual;
select trunc(dbms_random.value(1, 60)) into ss_i from dual;
create_time_i := to_date(year_i || '-' || month_i || '-' || day_i || ' ' || hh_i || ':' || mi_i || ':' || ss_i,
'yyyy-mm-dd hh24:mi:ss');
end if;
insert into temp
(id, name, salary, create_time)
values
(i, name_i, salary_i, create_time_i);
i := i + 1;
end loop;
commit;
exception
when others then
rollback;
dbms_output.put_line(sqlcode || ':' || sqlerrm);
end;
3、以表 temp 的字段 create_time 为查询条件进行降序排序查询，查询时采用分页形式，每页数据条数为5条。
查询第2页数据
select *
from (select rownum as rn, t.*
from (select p.id as id,
p.name as name,
p.salary as salary,
p.create_time as create_time
from temp p
where 1 = 1
order by p.create_time desc) t
where rownum <= 2 * 5) t1
where t1.rn >= 1 * 5;
下图为第2页查询结果
查询第3页数据
select *
from (select rownum as rn, t.*
from (select p.id as id,
p.name as name,
p.salary as salary,
p.create_time as create_time
from temp p
where 1 = 1
order by p.create_time desc) t
where rownum <= 3 * 5) t1
where t1.rn >= 2 * 5;
下图为第3页查询结果
很明显，第2页数据和第3页数据是完全重复的。实际上经过验证，第2、3、4、5、6页的数据是完全重复的。为什么会出现这样的情况呢？
经过分析，发现对表 temp 进行排序所使用的字段 create_time 有33条数据的值为空，当使用降序排序时默认会将这33条数据排在最前面。即前33条数据的 create_time 值均为空。可能是因为排序条件所使用的值有大量重复（均为空），所以不同分页之间的数据就出现了重复。
然后增加排序条件，在对 create_time 进行降序排序的前提下在增加一个主键（字段名为id）升序排序。因为主键是唯一值，在这样的查询条件下，不同分页之间应该不会出现数据重复。
重新排序后的SQL如下：
select *
from (select rownum as rn, t.*
from (select p.id as id,
p.name as name,
p.salary as salary,
p.create_time as create_time
from temp p
where 1 = 1
order by p.create_time desc,p.id asc) t
where rownum <= 3 * 5) t1
where t1.rn >= 2 * 5;
经过实际查询验证，每一页的数据均未再出现重现重复的情况。
4、以上是排序条件对应的字段值大量为空时，所出现的情况。如果排序条件对应的字段值大量非空相同时，是不是也如上所述，出现重复呢？
修改数据，将 create_time 为空的数据全部置为相同：
update temp set create_time = sysdate where create_time is null;
重新去掉按字段 id 升序的排序条件，执行SQL进行分页查询，发现第2、3页查询的结果相同。
下图为第2页查询结果
下图为第3页查询结果
实际上，在该查询条件下，第2、3、4、5、6页的数据也是完全重复的。
加上按字段 id 升序的排序条件以后，每一页的数据均未再出现重复。
总结：
在Oracle数据库中进行分页查询时，如果排序条件所对应的字段值有大量重复或为空时，则可能会出现分页数据重复的情况。如果要避免出现分页数据重复的情况，则一定要在排序时加上唯一值字段为排序条件。