https://blog.csdn.net/use_admin/article/details/83622414
作者使用了该博客的第二种经典写法好多年,忽然之间同事报过BUG来,说是这种分页漏掉数据,这让我很吃惊。
如下是经典写法:
SELECT *
FROM (SELECT tt.*, ROWNUM AS rowno
FROM ( SELECT t.*
FROM emp t
WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
AND TO_DATE ('20060731', 'yyyymmdd')
ORDER BY create_time DESC, emp_no) tt
WHERE ROWNUM <= 20) table_alias
WHERE table_alias.rowno >= 10;
我的写法如下:
SELECT U2.TRANSACTION_NBR,
FROM (select U1.TRANSACTION_NBR,
from (select T.TRANSACTION_NBR,
from PS_D_XXXXX_HDR T
where 1 = 1
ORDER BY T.CREATEDTTM DESC) U1
WHERE ROWNUM <= 40) U2
where U2.RN >= 20;
两者比较基本一致,奈何会漏数据呢?
经过我对数据的查询比较,发现了原因:
排序字段CREATEDTTM有很多相同的值,因此该排序不能确定唯一顺序。
修改后的SQL如下:
SELECT U2.TRANSACTION_NBR,
FROM (select U1.TRANSACTION_NBR,
from (select T.TRANSACTION_NBR,
from PS_D_XXXXX_HDR T
where 1 = 1
ORDER BY T.CREATEDTTM DESC, TRANSACTION_NBR desc) U1
WHERE ROWNUM <= 40) U2
where U2.RN >= 20;
确定唯一排序后,sql就没有问题了。