pl/sql利用rownum取得排序后的部分行序列

我們使用oracle的人都知道可以通過rownum偽列得到查詢結(jié)果序列前面的指定的行，為了下面更好的進行說明問題，我們先來創(chuàng)建一個數(shù)據(jù)表table1：

create table table1

(AAA integer primary key,

BBB varchar(30));

然后在table1中插入9條數(shù)據(jù)：

insert into table1 values (8, 'good');

insert into table1 values (7, 'morning');

insert into table1 values (20, 'afternoon');

insert into table1 values (2, 'have');

insert into table1 values (19, 'boy');

insert into table1 values (30, 'girl');

insert into table1 values (15, 'left');

insert into table1 values (26, 'think');

insert into table1 values (98, 'beautiful');

commit;

現(xiàn)在使用：

Select * from table1 where rownum < 4;

來得到前三個行。

AAA 　　　　BBB

8 good

7 morning

20 afternoon

這沒有問題，但如果你對rownum使用了大于號(>)，則查詢的結(jié)果集一定是空的。如：

Select * from table1 where rownum > 1;

無論表中有多少數(shù)據(jù)，都不會返回任何的數(shù)據(jù)。我們什么時候會用到rownum大于一個數(shù)字進行查詢呢，這里先賣一個關子，后面再說。

再來說說排序和rownum的關系：

如果使用

Select * from table1 where rownum < 4 Order by AAA;

我這里查詢出來的結(jié)果是：

AAA 　　　　BBB

7 morning

8 good

20 afternoon

和前面沒有Order By時的結(jié)果集數(shù)據(jù)是一樣的，只是排了一個序，而我們期望的結(jié)果是：

AAA 　　　　BBB

2 have

7 morning

8 good

那為什么會是這樣的一個結(jié)果，而又如何才能得到我們期望的結(jié)果呢？我們先要搞清楚rownum是如何生成的。現(xiàn)在使用下面的語句查詢一下：

select t.*, rownum from table1 t where rownum < 4 order by AAA ;

AAA 　　　　BBB 　　　　ROWNUM

7 morning 2

8 good 1

20 afternoon 3

發(fā)現(xiàn)rownum并不是按1,2,3的順序排列的，為什么會是這樣的一個結(jié)果呢？帶著這個問題，我們再使用下面的這個語句進行查詢：

select t.*, rownum, rowid from table1 t;

AAA BBB 　　Rownum 　　　　　　 RowID

8 good 　　　　1 AAAY8QAABAAAVIaAAA

7 morning 2 AAAY8QAABAAAVIaAAB

20 afternoon 3 AAAY8QAABAAAVIaAAC

2 have 4 AAAY8QAABAAAVIaAAD

19 boy 5 AAAY8QAABAAAVIaAAE

30 girl 6 AAAY8QAABAAAVIaAAF

15 left 7 AAAY8QAABAAAVIaAAG

26 think 　　　　8 AAAY8QAABAAAVIaAAH

98 beautiful 9 AAAY8QAABAAAVIaAAI

看到這個結(jié)果我們猜想，oracle是插入數(shù)據(jù)的同時就為每一行建立了一個惟一的rowid，并且是按插入的順序排序的，而rownum是按RowID進行排序的。為了證明我們的猜想是正確的，我們先刪除AAA為19的行，再插入另一個AAA為34的行，語句分別如下：

delete from table1 where aaa = 19;

insert into table1 values (34, 'like');

現(xiàn)在我們再使用select t.*, rownum, rowid from table1 t;進行查詢：

AAA BBB 　　　　　　Rownum 　　　　　　RowID

8 good 　　　　1 AAAY8QAABAAAVIaAAA

7 morning 2 AAAY8QAABAAAVIaAAB

20 afternoon 3 AAAY8QAABAAAVIaAAC

2 have 4 AAAY8QAABAAAVIaAAD

30 girl 5 AAAY8QAABAAAVIaAAF

15 left 6 AAAY8QAABAAAVIaAAG

26 think 　　　　7 AAAY8QAABAAAVIaAAH

98 beautiful 8 AAAY8QAABAAAVIaAAI

34 　　like 　　　　　　9 　　AAAY8QAABAAAVIaAAJ

結(jié)果證明我們的猜想是正確的：oracle是插入數(shù)據(jù)的同時就為每一行建立了一個惟一的rowid，并且是按插入的順序排序的，而rownum是按RowID進行排序的。

現(xiàn)在問題就出來了，既然rownum是按照rowid進行排序的，而rowid我們是不能改變的，也就是說我們不能改變rownum的排序方式，那么如何才能取得排序后的前n行呢？

為了得到我們期望的排序后的前n行數(shù)據(jù)，我們使用子查詢來實現(xiàn)，具體就是查詢另一個查詢語句返回的結(jié)果，sql語句如下：

select * from (select * from table1 t order by AAA) where rownum < 4 ;

返回的結(jié)果如下：

AAA BBB

2 have

7 morning

8 good

正是我們所期望的。

現(xiàn)在我們再來說說什么時候會用到rownum大于一個數(shù)字進行查詢，而又如何實現(xiàn)。我們先來設想一下，在一個web頁面上用一個table來顯示數(shù)據(jù)，假設一頁顯示4行數(shù)據(jù)，現(xiàn)在我們要顯示第二頁的數(shù)據(jù)，我們怎么實現(xiàn)呢？dotnet的datagrid和gridview使用的非常笨拙低效的辦法：把所有數(shù)據(jù)查出來，然后只顯示指定的數(shù)據(jù)。為了更有效的處理這個問題，我們想只查詢出來第二頁的數(shù)據(jù)，這就要用到rownum的大于查詢。我們期望使用

select * from (select * from table1 t order by AAA) where rownum > 4 and rownum <= 8;

來得到結(jié)果，可是前面說過，任何使用rownum大于一個數(shù)據(jù)的查詢，都不會返回任何的結(jié)果集。那我們又如何才能得到我們期望的結(jié)果呢？這就要使用一點技巧了，先使用：

select * from (select * from table1 t order by AAA) where rownum <= 8;

返回排序后的前8行數(shù)據(jù)，但只是這樣我們?nèi)詿o法再過濾掉前面的四行數(shù)據(jù)。為此我們要使用這個查詢的外層查詢的臨時rownum(可能有點拗口，再解釋一下，我們要使用這個rownum不是表table1的rownum，而查詢(select * from table1 t order by AAA)查詢結(jié)果集在查詢時生成的rownum)，并對它重命名，然后再對這個返回結(jié)果進行查詢，得到我們期望的結(jié)果，查詢語句如下：

Select * from (select a.*, rownum rn from (select * from table1 t order by AAA) where rownum <= 8) where rn > 4;

AAA BBB RN

20 afternoon 5

26 think 6

30 girl 7

34 like 8

這樣，我們就成功的實現(xiàn)了利用rownum查詢出排序后的中間部分行的功能。

在此我把這個功能完整的寫出來，希望對有用到這個功能的朋友有所幫助，謝謝。

原文：http://www.cnblogs.com/mickeychang/archive/2009/08/23/1552578.html

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學習有所幫助,可以手機掃描二維碼進行捐贈