方法一:
select top 10 b.* from (select top 20 主鍵字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主鍵字段 = a.主鍵字段 order by a.排序字段
10 = 每頁(yè)記錄數(shù)
20 = (當(dāng)前頁(yè) + 1) * 每頁(yè)記錄數(shù)
以上語(yǔ)句即可以實(shí)現(xiàn)分頁(yè)，但是最后取出的結(jié)果排序是升序，如果需要結(jié)果集為降序（例如時(shí)間），則有兩種方法可以處理
1.使用以下語(yǔ)句，但效率可能要降低一些
select * from 表名 b, (select top 10 主鍵字段,排序字段 from (select top 20 主鍵字段,排序字段 from 表名 order by 排序字段 desc) a order by 排序字段 ) c where b.主鍵字段 = c.主鍵字段 order by c.排序字段 desc
2.在ado里處理,將記錄集游標(biāo)移到最后，然后前移
''以下為asp范例
set rsTemp = Server.CreateObject("adodb.recordset")
rsTemp.Open 語(yǔ)句,conn,1,1
rsTemp.MoveLast
for i = 1 to rsTemp.RecordCount
?'取值....
rsTemp.MovePrevious
next
?
經(jīng)測(cè)試，以上分頁(yè)方法比使用臨時(shí)表分頁(yè)速度還要快，并且簡(jiǎn)單易用.
方法二：
大數(shù)據(jù)量下的分頁(yè)
??? 對(duì)于非常大的數(shù)據(jù)模型而言，分頁(yè)檢索時(shí)，每次都加載整個(gè)數(shù)據(jù)源非常浪費(fèi)。通常的選擇是檢索頁(yè)面大小的塊區(qū)的數(shù)據(jù)，而非檢索所有的數(shù)據(jù)，然后單步執(zhí)行當(dāng)前行。
??? 本文演示ASP.net的DataGrid和Sql Server 實(shí)現(xiàn)大數(shù)據(jù)量下的分頁(yè)，為了便于實(shí)現(xiàn)演示，數(shù)據(jù)表采用了Northwind數(shù)據(jù)庫(kù)的Orders表（830條記錄）。
??? 如果數(shù)據(jù)表中有唯一的自增索引，并且這個(gè)字段沒(méi)有出現(xiàn)斷號(hào)現(xiàn)象。檢索頁(yè)面大小的塊區(qū)數(shù)據(jù)就非常簡(jiǎn)單了。通過(guò)簡(jiǎn)單的Sql語(yǔ)句就可以實(shí)現(xiàn)這個(gè)功能：
select * from orders where orderid between 10248 and 10253
|
??? 其中，開(kāi)始編號(hào)為：(CurrentPageIndex - 1) * PageSize? 結(jié)束編號(hào)為：CurrentPageIndex * PageSize
??? 當(dāng)然，如果這個(gè)字段斷號(hào)不是很嚴(yán)重，而且允許不是很嚴(yán)格的按照每頁(yè)條數(shù)分頁(yè)，這樣的方法也是可以用的。
??? 如果這個(gè)字段斷號(hào)，或者需要按照其他條件排序分頁(yè)，就要復(fù)雜些了。首先要獲得這個(gè)頁(yè)面需要顯示的編號(hào)，然后再按照這個(gè)編號(hào)獲得需要的塊區(qū)數(shù)據(jù)。根據(jù)編號(hào)獲得塊區(qū)數(shù)據(jù)很簡(jiǎn)單。不過(guò)用下面方式獲得數(shù)據(jù)排序并不是按照指定的id列表順序，這時(shí)候還要附加order by 命令。
select * from orders where orderid in (10248,10249,10250,10251,10252,10253) order by orderid desc
|
??? 獲得這個(gè)頁(yè)面需要顯示的編號(hào)列表就復(fù)雜多了，而且有多種方案：
方案一：維護(hù)一個(gè)表，這個(gè)表記錄需要顯示的這些編號(hào)排序順序。（這個(gè)表可以是臨時(shí)表，也可以是物理表）。下面演示了利用一個(gè)全局臨時(shí)表。這個(gè)全局臨時(shí)表記錄需要顯示的編號(hào)。注意排序，這里的order by 就是需要顯示的排序順序。
create table ##temptable(iid int IDENTITY (1, 1) NOT NULL,mainid int NOT NULL)insert ##temptable(mainid) select OrderID from orders order by OrderID descselect * from ##temptabledrop table ##temptable -- 實(shí)際執(zhí)行時(shí)候，刪除全部臨時(shí)表當(dāng)然不再這里執(zhí)行。
|
這個(gè)臨時(shí)表存在，獲得指定分頁(yè)的分塊數(shù)據(jù)就很簡(jiǎn)單了。看下面代碼：
create table ##temptable(iid int IDENTITY (1, 1) NOT NULL,mainid int NOT NULL)insert ##temptable(mainid) select OrderID from orders order by OrderID descdeclare @PageSize int,@CurrPage int,@strSQL varchar(2000),@IDStr varchar(1000)select @PageSize = 30select @CurrPage = 2select @IDStr = ''select @IDStr = @IDStr + ltrim(rtrim(str(MainID))) + ',' from ##temptable where iid between ((@CurrPage-1)*@PageSize+1) and @CurrPage*@PageSizeif @IDStr <> '' begin select @IDStr = left(@IDStr,len(@IDStr)-1)endselect @strSQL = 'select * from orders where OrderID in ('+@IDStr+') order by OrderID desc 'exec(@strSQL)drop table ##temptable
|
注意：實(shí)際使用這個(gè)方案的時(shí)候，還要考慮何時(shí)更新這個(gè)全局臨時(shí)表，一般是放到計(jì)劃任務(wù)中，定時(shí)更新這個(gè)匯總表。
方案二：每次都去查詢，每次獲得最新的編號(hào)順序。由于這時(shí)候不存在這個(gè)臨時(shí)表，書寫獲得需要顯示頁(yè)面的編號(hào)的字符串就需要點(diǎn)技巧，看下面的代碼：
ASP.net 的 DataGrid 提供了使用這種分區(qū)的數(shù)據(jù)的方法。
DataGrid 通過(guò) AllowCustomPaging 和 VirtualItemCount 屬性支持塊區(qū)操作。如果 AllowCustomPaging 為 true，則 DataGrid 不會(huì)根據(jù) CurrentPageIndex 計(jì)算數(shù)據(jù)模型中的起始顯示位置。DataGrid 將顯示數(shù)據(jù)模型中的所有數(shù)據(jù)，而頁(yè)導(dǎo)航欄將當(dāng)前位置報(bào)告為 (VirtualItemCount+PageSize-1)/PageSize 之 CurrentPageIndex 頁(yè)。下面的示例說(shuō)明此功能。
declare @PageSize int,@CurrPage int,@topnum int,@previous intselect @PageSize = 30select @CurrPage = 2select @topnum = @CurrPage * @PageSizeselect @previous = (@CurrPage - 1) * @PageSizedeclare @i int,@IDStr nvarchar(500),@strSQL nvarchar(1000)select @i = 0select @strSQL = N''select @strSQL = @strSQL + N' select top '+str(@topnum)+ ' @i = @i + 1 'select @strSQL = @strSQL + N', @IdStr = 'select @strSQL = @strSQL + N'case when @i > '+str(@previous)+' then @IdStr + ltrim(rtrim(str(OrderID))) + '','' 'select @strSQL = @strSQL + N'else N''''end 'select @strSQL = @strSQL + N'from Orders 'select @strSQL = ltrim(rtrim(@strSQL)) + N' order by OrderID desc 'Select @IdStr = N''exec sp_executesql @strSQL,N'@i int,@IdStr varchar(500) output',@i,@IdStr outputif len(rtrim(ltrim(@IdStr))) > 0begin select @IdStr = left(@IdStr,len(@IdStr)-1)endselect @strSQL = 'select * from orders where OrderID in ('+@IDStr+')'exec(@strSQL)
|
?
protected void BindDataGrid(int currpage) { string strConn = "Data Source=(local);Integrated Security=SSPI;database=Northwind"; // 請(qǐng)確認(rèn) 機(jī)器名/ASPNET 用戶可以訪問(wèn)Northwind數(shù)據(jù)庫(kù) SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(strConn); SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@PageSize",SqlDbType.Int), new SqlParameter("@CurrPage",SqlDbType.Int), new SqlParameter("@SearchSql",SqlDbType.NVarChar,128), new SqlParameter("@Count",SqlDbType.Int), }; parms[0].Value = DataGrid1.PageSize; parms[1].Value = (currpage+1); // 數(shù)據(jù)庫(kù)的分頁(yè)算法第一頁(yè)是1 DataGrid的第一頁(yè)是0 parms[2].Value = DBNull.Value; parms[3].Direction = ParameterDirection.Output; parms[3].Value = DBNull.Value; DataSet DS = new DataSet(); try { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = "Selected_Page_List"; cmd.CommandType = CommandType.StoredProcedure; if (parms != null) { foreach (SqlParameter parm in parms) cmd.Parameters.Add(parm); } SqlDataAdapter DA = new SqlDataAdapter(cmd); DA.Fill(DS); int aa = Convert.ToInt32(parms[3].Value.ToString()); cmd.Parameters.Clear(); if (currpage == 0) { DataGrid1.VirtualItemCount = aa; } DataGrid1.CurrentPageIndex = currpage; DataGrid1.DataSource = DS; DataGrid1.DataBind(); } catch(Exception ewx) { conn.Close(); Response.Write (ewx.Message.ToString()); Response.End(); } } void Page_Load(Object sender, EventArgs E ) { if (!IsPostBack) { BindDataGrid(0); // 第一次打開(kāi)這個(gè)頁(yè)面，訪問(wèn)分頁(yè)的第一頁(yè) } } void MyDataGrid_Page(Object sender, DataGridPageChangedEventArgs e) { BindDataGrid(e.NewPageIndex); }
|
如果你有更多數(shù)據(jù)量的表稍加修改，也可以使用本演示程序。其下是演示代碼下載，演示代碼使用的是方案二。使用方法看readme.txt文件。
整個(gè)演示代碼 下載
http://chs.gotdotnet.com/quickstart/aspplus/samples/webforms/ctrlref/webctrl/datagrid/doc_datagrid.aspx#paging
這里演示了利用DataGrid 的這個(gè)功能(沒(méi)有本文中討論的利用存儲(chǔ)過(guò)程獲得分區(qū)數(shù)據(jù))。如對(duì)DataGrid的這個(gè)功能不太熟悉，請(qǐng)先看這里。
?
方法三:
雖然 DataGrid 控件自己帶了一個(gè)分頁(yè)處理機(jī)制，但它是將符合查詢條件的所有記錄讀入內(nèi)存，然后進(jìn)行分頁(yè)顯示的。隨著符合條件的記錄數(shù)目增多，就會(huì)出現(xiàn)運(yùn)行效率問(wèn)題，或者至少是資源的利用率下降。
下面的代碼示例都以下面的表結(jié)構(gòu)為準(zhǔn)：
| |
Articles 表 |
SQL Server 類型 |
Oracle 類型 |
| PK |
Id |
int (自增) |
number(9) (插入時(shí)在當(dāng)前最大值上加1) |
| |
Author |
nvarchar(10) |
nvarchar2(10) |
| |
Title |
nvarchar(50) |
nvarchar2(50) |
| |
PubTime |
datetime |
date |
SQL Server / Access 等微軟產(chǎn)品中，我們通常的自定義分頁(yè)有兩種思路：
一種是以 ASP.NET Forum 為代表的、“臨時(shí)表”方法：即在存儲(chǔ)過(guò)程中建立一個(gè)臨時(shí)表，該臨時(shí)表包含一個(gè)序號(hào)字段（1，2，3，....）以及表的主鍵（其他能夠唯一確定一行記錄的字段也是可以的）字段。存儲(chǔ)過(guò)程可能如下：（編號(hào) SS1）
CREATE Procedure GetAllArticles_Paged
(
???? @PageIndex int,
???? @PageSize int,
???? @TotalRecords out int,
???? @TotalPages out int
)
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #tmp
(
???? RecNo int IDENTITY (1, 1) NOT NULL,
???? ArticleID int
)
INSERT INTO #tmp
???? SELECT [ID]
???? FROM Articles
???? ORDER BY PubTime DESC
SELECT A.*
FROM Articles A (nolock), #tmp T
WHERE A.ID = T.ArticleID AND
???? T.RecNo > @PageLowerBound AND
???? T.RecNo < @PageUpperBound
ORDER BY T.RecNo
GO
另一種可能更適合程序中“拼湊” SQL 語(yǔ)句：用兩次 TOP 命令取得我們所要的分頁(yè)數(shù)據(jù)，例如：（編號(hào) SS2）
SELECT * FROM
???? (
???? SELECT TOP(PageSize) * FROM
???? (
????????? SELECT TOP (PageSize * PageIndex) *
????????? FROM Articles
????????? ORDER BY PubTime DESC
???? )
???? ORDER BY PubTime ASC
)
ORDER BY PubTime DESC
這個(gè)的想法就是“掐頭去尾”，還有不少分頁(yè)的方法，這里就不一一列出了。
對(duì)于 Oracle 數(shù)據(jù)庫(kù)，有幾處不同嚴(yán)重妨礙了上面幾個(gè)方法的實(shí)施，比如，Oracle 不支持 TOP 關(guān)鍵字：不過(guò)這個(gè)好像并不十分嚴(yán)重，因?yàn)樗峁┝?rownum 這個(gè)隱式游標(biāo)，可以實(shí)現(xiàn)與 TOP 類似的功能，如：
SELECT TOP 10 ... FROM WHERE ...
要寫成
SELECT ... FROM ... WHERE ... AND rownum <= 10
rownum 是記錄序號(hào)（1，2，3...），但有一個(gè)比較麻煩的事情是：如果 SQL 語(yǔ)句中有 ORDER BY ... 排序的時(shí)候，rownum 居然是先“標(biāo)號(hào)”后排序！這樣，這個(gè)序號(hào)如果不加處理是不合乎使用需求的。
至于臨時(shí)表，Oracle 的臨時(shí)表和 SQL Server 的有很大不同，我還沒(méi)搞懂這個(gè)東西，就不妄加揣測(cè)了。
國(guó)內(nèi)網(wǎng)站中介紹 Oracle 分頁(yè)的資料很少，我找到了一個(gè)國(guó)外站點(diǎn)（www.faqts.com）的一篇 FAQ，根據(jù)這篇文章的介紹，可以如下分頁(yè)：（編號(hào) Ora1）
SELECT * FROM
???? (
???? SELECT A.*, rownum r
???? FROM
????????? (
????????? SELECT *
????????? FROM Articles
????????? ORDER BY PubTime DESC
????????? ) A
???? WHERE rownum <= PageUpperBound
???? ) B
WHERE r > PageLowerBound;
其中藍(lán)色部分可以改為任意的、需要的 SQL SELECT 語(yǔ)句，這點(diǎn)倒是挺方便的。
方法四:
今天突然發(fā)現(xiàn)，Oracle原來(lái)可以這樣實(shí)現(xiàn)分頁(yè)功能：
select * from (select rownum rdd,field1,field2?from t_table where rownum<=400) where? rdd>200
上述語(yǔ)句實(shí)現(xiàn)了從第201條記錄開(kāi)始處取200條記錄