oracle对查询结果去重,Oracle 分页查询与数据去重深入理解

一、效率高的写法

**

1.无ORDER BY排序的写法。(效率最高)

(经过测试，此方法成本最低，只嵌套一层，速度最快！即使查询的数据量再大，也几乎不受影响，速度依然！)

SELECT *

FROM (SELECT ROWNUM AS rowno, t.*

FROM emp t

WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

AND TO_DATE ('20060731', 'yyyymmdd')

AND ROWNUM <= 20) table_alias

WHERE table_alias.rowno >= 10;

2.有ORDER BY排序的写法。(效率较高)

(经过测试，此方法随着查询范围的扩大，速度也会越来越慢哦！)

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 >=