我們使用Oracle的人都知道可以通過rownum偽列得到查詢結果序列前面的指定的行,為了下面更好的進行說明問題,我們先來創建一個數據表table1:
createtable table1
(AAA integerprimarykey,
BBB varchar(30));
然後在table1中插入9條數據:
insertinto table1 values (8, 'good');
insertinto table1 values (7, 'morning');
insertinto table1 values (20, 'afternoon');
insertinto table1 values (2, 'have');
insertinto table1 values (19, 'boy');
insertinto table1 values (30, 'girl');
insertinto table1 values (15, 'left');
insertinto table1 values (26, 'think');
insertinto table1 values (98, 'beautiful');
commit;
現在使用:
Select * from table1 where rownum < 4;
來得到前三個行。
AAA BBB
8 good
7 morning
20 afternoon
這沒有問題,但如果你對rownum使用了大於號(>),則查詢的結果集一定是空的。如:
Select * from table1 where rownum > 1;
無論表中有多少數據,都不會返回任何的數據。我們什麼時候會用到rownum大於一個數字進行查詢呢,這裡先賣一個關子,後面再說。
再來說說排序和rownum的關系:
如果使用
Select * from table1 where rownum < 4 Order by AAA;
我這裡查詢出來的結果是:
AAA BBB
7 morning
8 good
20 afternoon
和前面沒有Order By時的結果集數據是一樣的,只是排了一個序,而我們期望的結果是:
AAA BBB
2 have
7 morning
8 good
那為什麼會是這樣的一個結果,而又如何才能得到我們期望的結果呢?我們先要搞清楚rownum是如何生成的。現在使用下面的語句查詢一下:
select t.*, rownum from table1 t whererownum < 4 orderby AAA ;
AAA BBB ROWNUM
7 morning 2
8 good 1
20 afternoon 3
發現rownum並不是按1,2,3的順序排列的,為什麼會是這樣的一個結果呢?帶著這個問題,我們再使用下面的這個語句進行查詢:
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
看到這個結果我們猜想,Oracle是插入數據的同時就為每一行建立了一個惟一的rowid,並且是按插入的順序排序的,而rownum是按RowID進行排序的。為了證明我們的猜想是正確的,我們先刪除AAA為19的行,再插入另一個AAA為34的行,語句分別如下:
deletefrom table1 where aaa = 19;
insertinto table1 values (34, 'like');
現在我們再使用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
結果證明我們的猜想是正確的:Oracle是插入數據的同時就為每一行建立了一個惟一的rowid,並且是按插入的順序排序的,而rownum是按RowID進行排序的。
現在問題就出來了,既然rownum是按照rowid進行排序的,而rowid我們是不能改變的,也就是說我們不能改變rownum的排序方式,那麼如何才能取得排序後的前n行呢?
為了得到我們期望的排序後的前n行數據,我們使用子查詢來實現,具體就是查詢另一個查詢語句返回的結果,sql語句如下:
select * from (select * from table1 t orderby AAA) whererownum < 4 ;
返回的結果如下:
AAA BBB
2 have
7 morning
8 good
正是我們所期望的。
現在我們再來說說什麼時候會用到rownum大於一個數字進行查詢,而又如何實現。我們先來設想一下,在一個web頁面上用一個table來顯示數據,假設一頁顯示4行數據,現在我們要顯示第二頁的數據,我們怎麼實現呢?dotnet的datagrid和gridvIEw使用的非常笨拙低效的辦法:把所有數據查出來,然後只顯示指定的數據。為了更有效的處理這個問題,我們想只查詢出來第二頁的數據,這就要用到rownum的大於查詢。我們期望使用
select * from (select * from table1 t orderby AAA) whererownum > 4 and rownum <= 8;
來得到結果,可是前面說過,任何使用rownum大於一個數據的查詢,都不會返回任何的結果集。那我們又如何才能得到我們期望的結果呢?這就要使用一點技巧了,先使用:
select * from (select * from table1 t orderby AAA) where rownum <= 8;
返回排序後的前8行數據,但只是這樣我們仍無法再過濾掉前面的四行數據。為此我們要使用這個查詢的外層查詢的臨時rownum(可能有點拗口,再解釋一下,我們要使用這個rownum不是表table1的rownum,而查詢(select * from table1 t orderby AAA)查詢結果集在查詢時生成的rownum),並對它重命名,然後再對這個返回結果進行查詢,得到我們期望的結果,查詢語句如下:
Select * from (select a.*, rownum rn from (select * from table1 t orderby AAA) where rownum <= 8) where rn > 4;
AAA BBB RN
20 afternoon 5
26 think 6
30 girl 7
34 like 8
這樣,我們就成功的實現了利用rownum查詢出排序後的中間部分行的功能。
在此我把這個功能完整的寫出來,希望對有用到這個功能的朋友有所幫助。