Oracle中的SQL分頁查詢原理和方法詳解

本文分析并介紹Oracle中的分頁查找的方法。

Oracle中的表，除了我們建表時設計的各個字段，其實還有兩個字段（此處只介紹2個），分別是ROWID（行標示符）和ROWNUM（行號），即使我們使用DESCRIBE命令查看表的結構，也是看不到這兩個列的描述的，因為，他們其實是只在數據庫內部使用的，所以也通常稱他們為偽列（pseudo column）。

下面我們先建表并添加一些數據來驗證上面的說明。

建表：create table users(id integer primary key,name nvarchar2(20))

插入數據：insert into users(id,name) values(1,'tom');insert into users(id,name) values(2,'cat');insert into users(id,name) values(3,'bob');insert into users(id,name) values(4,'anxpp');insert into users(id,name) values(5,'ez');insert into users(id,name) values(6,'lily');

使用describe命令查看表結構：

可以看到，確實只有建表時的兩個字段。

但我們可以查詢的時候，查找到偽列的值：select rowid,rownum,id,name from users;

結果：

這個rowid我們一般用不到，Oracle數據庫內部使用它來存儲行的物理位置，是一個18位的數字，采用base-64編碼。

而這個rownum，我們也正是使用它來進行分頁查詢的，它的值，就是表示的該行的行號。

對于分頁，我們只要想辦法可以查詢到從某一起始行到終止行就可以的，分頁的邏輯可以放到程序里面。連云港數據恢復

于是，我們理所當然會想到如下語句查詢第2頁的數據（每頁2條數據，頁碼從1開始，所以起始行的行號為 (頁碼-1)*每頁長度+1=3,終止行的行號為 頁碼*每頁長度=4）：select * from users where rownum>=3 rownum <= 4;

哈哈！是不是發現沒有任何結果，原因很簡單，Oracle機制就是這樣的：因為第一條數據行號為1，不符合>=3的條件，所以第一行被去掉，之前的第二行變為新的第一行（即這個行號不是寫死的，可以理解為是動態的），如此下去，一直到最后一行，條件始終沒法滿足，所以就一條數據也查不出來。

既然找到了原因，解決方法也就很明顯了，我們只要將行號查詢出來生成一個結果集，然后再從這個結果集中，選擇行號大于我們設定的那個值就可以了，上面的分頁查找正確的寫法應該是這樣：select id,name from(select rownum rn,u.* from users u) uawhere ua.rn between 3 and 4;

上面的語句還可以優化：>=不能用，但是<=是可以的，我們不需要在子查詢中將結果全部查出來，首先使用終止行篩選子查詢的結果，SQL如下：select id,name from(select rownum rn,u.* from users u where rownum<=4) uawhere ua.rn >= 3;

結果：

很多時候，我們并不是盲目的分頁查找的，二十按某一個或多個字段的升序或降序分頁，即包含 order by 語句的分頁查詢，我們先看一下 order by 的查詢結果中rownum是怎樣的：select rownum,id,name from users order by name;

結果：

可以看到，我們說行號完全是動態的，也是不準確的，這時候的行號并不是經過 order by 后新結果的增序行號。

但有了上面的嵌套查詢的經驗，這里也可以好好應用一下，怎么做呢：先查找出排序好的結果集，然后應用上面的方法得到最終結果，sql如下：select id,name from((select rownum rn,uo.* from(select * from users u order by name) uowhere rownum<=4)) uawhere ua.rn>=3;

按照上面的結果，正確的分頁結果應該是id為2和5的，看下結果：

OK，結果正確。

其實連表查詢之類的，也是差不多的，多點嵌套而已，掌握了原理，隨便分析一下就能寫出對應的SQL了，而編寫SQL時，我們也得動動腦子，畢竟SQL也是由優劣之分的。