oracle 分頁問題

工作中遇到問題：分頁查詢兩個頁面查詢的資料一樣，

select *
from
    (
    select rownum as rw,a1.*
    from (
        .....................
        )
        order by mdi.createdate desc
        ) a1
    where rownum<=30
    ) m 
where  rw>=16

查網後發現：

測試：

表結構及資料如下：
create table my_test
(
  ID   NUMBER(10) not null,
  TYPE NUMBER(1) not null,
  NAME VARCHAR2(50) not null
);
insert into my_test (ID, TYPE, NAME) values (1, 0, 'A');
insert into my_test (ID, TYPE, NAME) values (2, 1, 'B');
insert into my_test (ID, TYPE, NAME) values (3, 1, 'C');
insert into my_test (ID, TYPE, NAME) values (4, 0, 'D');
insert into my_test (ID, TYPE, NAME) values (5, 0, 'E');
insert into my_test (ID, TYPE, NAME) values (6, 1, 'F');
insert into my_test (ID, TYPE, NAME) values (7, 0, 'G');
insert into my_test (ID, TYPE, NAME) values (8, 1, 'H');
insert into my_test (ID, TYPE, NAME) values (9, 1, 'I');
insert into my_test (ID, TYPE, NAME) values (10, 0, 'J');
commit;

我的SQL查詢語句為：
select * from my_test order by TYPE ASC

結果顯示為
1 0 A
5 0 E
7 0 G
10 0 J
4 0 D
6 1 F
8 1 H
9 1 I
3 1 C
2 1 B

如果我只想取出前4條記錄，使用如下SQL：
select tbl.* from (select * from my_test order by TYPE ASC) tbl where rownum <= 4

根據前面查詢的結果，資料應該是
1 0 A
5 0 E
7 0 G
10 0 J

但是實際上確不是這樣：

1 0 A
4 0 D
5 0 E
7 0 G

並且，如果我設定 rownum <= 5，結果又有所不同
select tbl.* from (select * from my_test order by TYPE ASC) tbl where rownum <= 5

1 0 A
4 0 D
5 0 E
10 0 J
7 0 G

ID為10的記錄跑到ID為7的記錄前面了。
換句話說，結果集由於rownum限制不同而改變了排列順序。

只要 order by 子句中的欄位不能唯一確定查詢結果的順序時，使用rownum 限制記錄數量會造成排序混亂的問題。

這個問題會使資料在分頁顯示時無法正確進行，不同頁面間顯示的資料可能會包含同一條記錄。

解決辦法就是在order by 子句中再增加一個可以唯一確定順序的欄位（例如 order by TYPE ASC, ID ASC）

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25323853/viewspace-714594/，如需轉載，請註明出處，否則將追究法律責任。