Ⅰ Oracle常見sql分頁實現方案
在Oracle中 用SQL來實現分頁有很多種實現方式 但有些語句可能並不是很通用 只能用在一些特殊場景之中
以下介紹三種比較通用的實現方案 在以下各種實現中 ROWNUM是一個最核心的關鍵詞 在查詢時他是一個虛擬的列 取值為 到記錄總數的序號
首先來介紹我們工作中最常使用的一種實現方式
SELECT *
FROM (SELECT ROW_ * ROWNUM ROWNUM_
FROM (SELECT *
FROM TABLE
WHERE TABLE _ID = XX
ORDER BY GMT_CREATE DESC) ROW_
WHERE ROWNUM <= )
WHERE ROWNUM_ >= ;
其中最內層的查詢SELECT為不進行翻頁的原始查詢語句 可以用自己的任意Select SQL替換 ROWNUM <= 和ROWNUM >= 控制分頁查詢的每頁的范圍
分頁的目的就是控制輸出結果集大小 將結果盡快的返回 上面的SQL語句在大多數情況擁有較高的效率 主要體現在WHERE ROWNUM <= 這句上 這樣就控制了查詢過程中的最大記錄數
上面例子中展示的在查詢的第二層通過ROWNUM <= 來控制最大值 在查詢的最外層控制最小值 而另一種方式是去掉查詢第二層的WHERE ROWNUM <= 語句 在查詢的最外層控制分頁的最小值和最大值 此時SQL語句如下 也就是要介紹的第二種實現方式
SELECT *
FROM (SELECT A * ROWNUM RN
FROM (SELECT *
FROM TABLE
WHERE TABLE _ID = XX
ORDER BY GMT_CREATE DESC) A)
WHERE RN BEEEN AND ;
由於Oracle可以將外層的查詢條件推到內層查詢中 以提高內層查詢的執行效率 但不能跨越多層
對於第一個查詢語句 第二層的查詢條件WHERE ROWNUM <= 就可以被Oracle推入到內層查詢中 這樣Oracle查詢的結果一旦超過了ROWNUM限制條件 就終止查詢將結果返回了
而 第二個查詢語句 由於查詢條件BEEEN AND 是存在於查詢的第三層 而Oracle無法將第三層的查詢條件推到最內層（即使推到最內層也沒有意義 因為最內層查詢不知道RN代表什麼） 因此 對於第二個查詢語句 Oracle最內層返回給中間層的是所有滿足條件的數據 而中間層返回給最外層的也是所有數據 數據的過濾在最外層完成 顯然這個效率要比第一個查詢低得多
以上兩種方案完全是通過ROWNUM來完成 下面一種則採用ROWID和ROWNUM相結合的方式 SQL語句如下
SELECT *
FROM (SELECT RID
FROM (SELECT R RID ROWNUM LINENUM
FROM (SELECT ROWID RID
FROM TABLE
WHERE TABLE _ID = XX
ORDER BY GMT_CREATE DESC) R
WHERE ROWNUM <= )
WHERE LINENUM >= ) T
TABLE T
WHERE T RID = T ROWID;
從語句上看 共有 層Select嵌套查詢 最內層為可替換的不分頁原始SQL語句 但是他查詢的欄位只有ROWID 而沒有任何待查詢的實際表欄位 具體查詢實際欄位值是在最外層實現的
這種方式的原理大致為 首先通過ROWNUM查詢到分頁之後的 條實際返回記錄的ROWID 最後通過ROWID將最終返回欄位值查詢出來並返回
和前面兩種實現方式相比 該SQL的實現方式更加繁瑣 通用性也不是非常好 因為要將原始的查詢語句分成兩部分（查詢欄位在最外層 表及其查詢條件在最內層）
但這種實現在特定場景下還是有優勢的 比如我們經常要翻頁到很後面 比如 條記錄中我們經常需要查 及其以後的數據 此時該方案效率可能要比前面的高
因為前面的方案中是通過ROWNUM <= 來控制的 這樣就需要查詢出 條數據 然後取最後 之間的數據 而這個方案直接通過ROWID取需要的那 條數據
從不斷向後翻頁這個角度來看 第一種實現方案的成本會越來越高 基本上是線性增長 而第三種方案的成本則不會像前者那樣快速 他的增長只體現在通過查詢條件讀取ROWID的部分
當然 除了以上提了這些方案 我們還可以用以下的SQL來實現
SELECT *
FROM TABLE
WHERE TABLE _ID NOT IN
(SELECT TABLE _ID FROM TABLE WHERE ROWNUM <= )
AND ROWNUM <= ;
SELECT *
FROM TABLE
WHERE ROWNUM <=
MINUS
SELECT * FROM TABLE WHERE ROWNUM <= ;
………………
注意 當ROWNUM作為查詢條件時 他是在order by之前執行 所以要特別小心
比如我們想查詢TABLE 中按TABLE _ID倒序排列的前 條記錄不能用如下的SQL來完成
lishixin/Article/program/Oracle/201311/11198
Ⅱ 用SQL寫出分頁查詢
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A --不排序
WHERE ROWNUM <= 40
)
WHERE RN >= 21;
SELECT *
FROM (SELECT a.*, row_number() over(ORDER BY 1) rn--假排序，速度同上
FROM t1 a)
WHERE rn BETWEEN 21 AND 40;
SELECT *
FROM (SELECT a.*, row_number() over(ORDER BY c1) rn --真實排序，無法比較速度
FROM t1 a)
WHERE rn BETWEEN 21 AND 40;
Oracle的分頁查詢語句基本上可以按照本文給出的格式來進行套用。
分頁查詢格式：
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
其中最內層的查詢SELECT * FROM TABLE_NAME表示不進行翻頁的原始查詢語句。ROWNUM <= 40和RN >= 21控制分頁查詢的每頁的范圍。
上面給出的這個分頁查詢語句，在大多數情況擁有較高的效率。分頁的目的就是控制輸出結果集大小，將結果盡快的返回。在上面的分頁查詢語句中，這種考慮主要體現在WHERE ROWNUM <= 40這句上。
選擇第21 到40條記錄存在兩種方法，一種是上面例子中展示的在查詢的第二層通過ROWNUM <= 40來控制最大值，在查詢的最外層控制最小值。而另一種方式是去掉查詢第二層的WHERE ROWNUM <= 40語句，在查詢的最外層控制分頁的最小值和最大值。這是，查詢語句如下：
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40
對比這兩種寫法，絕大多數的情況下，第一個查詢的效率比第二個高得多。
這是由於CBO 優化模式下，Oracle可以將外層的查詢條件推到內層查詢中，以提高內層查詢的執行效率。對於第一個查詢語句，第二層的查詢條件WHERE ROWNUM <= 40就可以被Oracle推入到內層查詢中，這樣Oracle查詢的結果一旦超過了ROWNUM限制條件，就終止查詢將結果返回了。
而第二個查詢語句，由於查詢條件BETWEEN 21 AND 40是存在於查詢的第三層，而Oracle無法將第三層的查詢條件推到最內層（即使推到最內層也沒有意義，因為最內層查詢不知道RN代表什麼）。因此，對於第二個查詢語句，Oracle最內層返回給中間層的是所有滿足條件的數據，而中間層返回給最外層的也是所有數據。數據的過濾在最外層完成，顯然這個效率要比第一個查詢低得多。
上面分析的查詢不僅僅是針對單表的簡單查詢，對於最內層查詢是復雜的多表聯合查詢或最內層查詢包含排序的情況一樣有效。
這里就不對包含排序的查詢進行說明了，下一篇文章會通過例子來詳細說明。下面簡單討論一下多表聯合的情況。對於最常見的等值表連接查詢，CBO 一般可能會採用兩種連接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不會考慮）。在這里，由於使用了分頁，因此指定了一個返回的最大記錄數，NESTED LOOP在返回記錄數超過最大值時可以馬上停止並將結果返回給中間層，而HASH JOIN必須處理完所有結果集（MERGE JOIN也是）。那麼在大部分的情況下，對於分頁查詢選擇NESTED LOOP作為查詢的連接方法具有較高的效率（分頁查詢的時候絕大部分的情況是查詢前幾頁的數據，越靠後面的頁數訪問幾率越小）。
因此，如果不介意在系統中使用HINT的話，可以將分頁的查詢語句改寫為：
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
Ⅲ 如何用sql語句 實現分頁查詢
適用於 SQL Server 2000/2005
SELECT TOP 頁大小 *
FROM table1
WHERE id NOT IN
SELECT TOP 頁大小*(頁數-1) id FROM table1 ORDER BY id
Ⅳ 用SQL語句怎麼實現資料庫分頁
CREATE
PROC
sp_PageView
@tbname
sysname,
--要分頁顯示的表名
@FieldKey
nvarchar(1000),
--用於定位記錄的主鍵(惟一鍵)欄位,可以是逗號分隔的多個欄位
@PageCurrent
int=1,
--要顯示的頁碼
@PageSize
int=10,
--每頁的大小(記錄數)
@FieldShow
nvarchar(1000)='',
--以逗號分隔的要顯示的欄位列表,如果不指定,則顯示所有欄位
@FieldOrder
nvarchar(1000)='',
--以逗號分隔的排序欄位列表,可以指定在欄位後面指定DESC/ASC用於指定排序順序
@Where
nvarchar(1000)='',
--查詢條件
@PageCount
int
OUTPUT
--總頁數
AS
SET
NOCOUNT
ON
--檢查對象是否有效
IF
OBJECT_ID(@tbname)
IS
NULL
BEGIN
RAISERROR(N'對象"%s"不存在',1,16,@tbname)
RETURN
END
IF
OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND
OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND
OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、視圖或者表值函數',1,16,@tbname)
RETURN
END
--分頁欄位檢查
IF
ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分頁處理需要主鍵（或者惟一鍵）',1,16)
RETURN
END
--其他參數檢查及規范
IF
ISNULL(@PageCurrent,0)<1
SET
@PageCurrent=1
IF
ISNULL(@PageSize,0)<1
SET
@PageSize=10
IF
ISNULL(@FieldShow,N'')=N''
SET
@FieldShow=N'*'
IF
ISNULL(@FieldOrder,N'')=N''
SET
@FieldOrder=N''
ELSE
SET
@FieldOrder=N'ORDER
BY
'+LTRIM(@FieldOrder)
IF
ISNULL(@Where,N'')=N''
SET
@Where=N''
ELSE
SET
@Where=N'WHERE
('+@Where+N')'
--如果@PageCount為NULL值,則計算總頁數(這樣設計可以只在第一次計算總頁數,以後調用時,把總頁數傳回給存儲過程,避免再次計算總頁數,對於不想計算總頁數的處理而言,可以給@PageCount賦值)
IF
@PageCount
IS
NULL
BEGIN
DECLARE
@sql
nvarchar(4000)
SET
@sql=N'SELECT
@PageCount=COUNT(*)'
+N'
FROM
'+@tbname
+N'
'+@Where
EXEC
sp_executesql
@sql,N'@PageCount
int
OUTPUT',@PageCount
OUTPUT
SET
@PageCount=(@PageCount+@PageSize-1)/@PageSize
END
--計算分頁顯示的TOPN值
DECLARE
@TopN
varchar(20),@TopN1
varchar(20)
SELECT
@TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize
--第一頁直接顯示
IF
@PageCurrent=1
EXEC(N'SELECT
TOP
'+@TopN
+N'
'+@FieldShow
+N'
FROM
'+@tbname
+N'
'+@Where
+N'
'+@FieldOrder)
ELSE
BEGIN
--生成主鍵(惟一鍵)處理條件
DECLARE
@Where1
nvarchar(4000),@s
nvarchar(1000)
SELECT
@Where1=N'',@s=@FieldKey
WHILE
CHARINDEX(N',',@s)>0
SELECT
@s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
@Where1=@Where1
+N'
AND
a.'+LEFT(@s,CHARINDEX(N',',@s)-1)
+N'='+LEFT(@s,CHARINDEX(N',',@s)-1)
SELECT
@Where1=STUFF(@Where1+N'
AND
a.'+@s+N'='+@s,1,5,N''),
@TopN=@TopN1-@PageSize
--執行查詢
EXEC(N'SET
ROWCOUNT
'+@TopN1
+N'
SELECT
'+@FieldKey
+N'
INTO
#
FROM
'+@tbname
+N'
'+@Where
+N'
'+@FieldOrder
+N'
SET
ROWCOUNT
'+@TopN
+N'
DELETE
FROM
#'
+N'
SELECT
'+@FieldShow
+N'
FROM
'+@tbname
+N'
a
WHERE
EXISTS(SELECT
*
FROM
#
WHERE
'+@Where1
+N')
'+@FieldOrder)
END
Ⅳ 在oracle資料庫中的分頁SQL語句怎麼寫
前提：
分頁參數：size = 20 page = 2；
沒有order by的查詢；
嵌套子查詢，兩次篩選（推薦使用）。
SQL語句：
SELECT *
FROM (SELECT ROWNUM AS rowno, t.*
FROM DONORINFO t
WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')
AND TO_DATE ('20060731', 'yyyymmdd')
AND ROWNUM <= 20*2) table_alias
WHERE table_alias.rowno > 20*(2-1);
(5)如何用sql語句實現分頁查詢擴展閱讀：
rownum總是從1開始的，第一條不滿足去掉的話，第二條的rownum 又成了1。依此類推，所以永遠沒有不滿足條件的記錄。
可以這樣理解：rownum是一個序列，是Oracle資料庫從數據文件或緩沖區中讀取數據的順序。它取得第一條記錄則rownum值為1，第二條為2。
依次類推：當使用「>、>=、=、between...and」這些條件時，從緩沖區或數據文件中得到的第一條記錄的rownum為1，不符合sql語句的條件，會被刪除，接著取下條。下條的rownum還會是1，又被刪除，依次類推，便沒有了數據。
Ⅵ SQL如何實現數據分頁,要具體語句,謝謝
可以的，用存儲過程
分頁存儲過程如下
CREATE PROCEDURE GetRecordFromPage
@tblName varchar(255), -- 表名
@RetColumns varchar(1000) = '*', -- 需要返回的列，默認為全部
@Orderfld varchar(255), -- 排序欄位名
@PageSize int = 10, -- 頁尺寸
@PageIndex int = 1, -- 頁碼
@IsCount bit = 0, -- 返回記錄總數, 非 0 值則返回
@OrderType varchar(50) = 'asc', -- 設置排序類型, 非 asc 值則降序
@strWhere varchar(1000) = '' -- 查詢條件 (注意: 不要加 where)
AS
declare @strSQL varchar(1000) -- 主語句
declare @strTmp varchar(300) -- 臨時變數
declare @strOrder varchar(400) -- 排序類型
if @IsCount != 0 --執行總數統計
begin
if @strWhere != ''
set @strSQL = "select count(*) as Total from [" + @tblName + "] where " + @strWhere
else
set @strSQL = "select count(*) as Total from [" + @tblName + "]"
end
else --執行查詢操作
begin
if @OrderType != 'asc'
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @Orderfld +"] desc"
end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by [" + @Orderfld +"] asc"
end
set @strSQL = "select top " + str(@PageSize) + " " + @RetColumns + " from ["
+ @tblName + "] where [" + @Orderfld + "]" + @strTmp + "(["
+ @Orderfld + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @Orderfld + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"
+ @strOrder
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) + " " + @RetColumns + " from ["
+ @tblName + "] where [" + @Orderfld + "]" + @strTmp + "(["
+ @Orderfld + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @Orderfld + "] from [" + @tblName + "] where (" + @strWhere + ") "
+ @strOrder + ") as tblTmp) and (" + @strWhere + ") " + @strOrder
if @PageIndex = 1
begin
set @strTmp = ""
if @strWhere != ''
set @strTmp = " where (" + @strWhere + ")"
set @strSQL = "select top " + str(@PageSize) + " " + @RetColumns + " from ["
+ @tblName + "]" + @strTmp + " " + @strOrder
end
end
exec (@strSQL)
Ⅶ 【SQL語句】-分頁查詢limit的用法
select * from table limit 索引 , 查詢的數據個數
select grade from Student limit 5,1。表示：從第6行或者第6頁開始查詢（包括第6行），往後查一行數據，結果是 6.
select * from Customer limit 10 ;--檢索前10行數據，顯示1-10條數據
=select * from Customer limit 0,10 --0可以省略
select * from Customer limit 5,10 ;--檢索從第6行開始向後加10條數據，共顯示id為6,7....15
查詢從某一行開始到結尾的全部數據，可以在第二個參數中設置一個很大的值
如：查詢從第3行開始的後面全部數據
select * from table limit 2,99999999999999999999999999;
擴展：
limit典型的應用場景就是實現分頁查詢
已知：每頁顯示m條數據，求：顯示第n頁的數據
select * from table limit (n-1)m,m
Ⅷ 如何使用sql語句進行分頁操作
利用SQL語句分頁要看你用的什麼資料庫。
Oracle資料庫可以使用ROWNUM或row_number()，例如：Select
*
from
(select
ROWNUM
rn,
t.*
from
table
t)
where
rn
between
11
and
20;
Select
*
from
(select
row_number()
over
(ORDER
BY
col1)
rn,
t.*
from
table
t)
where
rn
between
11
and
20;
SQLServer資料庫可以用Top或者row_number()函數，道理同上。
利用SQL分頁有局限性，就是針對不同的資料庫有不同的寫法，所以通常會在應用程序裡面做分頁通用性比較強。但是對於數據量非常龐大的應用來說，還是用SQL分頁比較適合。
Ⅸ 使用sql語句實現分頁查詢
使用sql語句在語句末尾添加 limit page,count //意思是 獲取從page+1開始的count條記錄
例如：select * from checkmoney where phonenumber='18209183861' order by check_start_time desc limit 1,5;
這條語句獲取了 checkmoney的第 2條至第6條 記錄。