2008年10月16日 星期四

在 SQL 實作分頁效果




declare @pagesize nvarchar(20) 
declare @pageindex nvarchar(20)
set @pagesize='10' --每一頁呈現的資料筆數
set @pageindex='2' --要查詢的頁面索引(從0開始)

declare @sql nvarchar(2000)

-- 在 SQL 2005 提供了 ROW_NUMBER 函數,自動產生序號,如果是 SQL 2000,則需自行產生相類似的功能 (如註一)

select ROW_NUMBER() OVER(ORDER BY com_empno DESC) AS 'Row_Number',* 
into #tmp from common..comper 

set @sql='select Top '+@pagesize+' * from #tmp '
set @sql=@sql+'where Row_Number NOT IN ( '
set @sql=@sql+'select Top ('+@pagesize+'*'+@pageindex+')  Row_Number from #tmp )'

print @sql
exec (@sql)

select count(*) from common..comper 

drop table #tmp



-- 列印出 剛剛所執行的 SQL 語法

select Top 10 * from #tmp 
where 
Row_Number NOT IN 
( select Top (10*2)  Row_Number from #tmp )


#tmp 是我們自行整理過含有自動編號的資料集合
假如每個頁面要顯示10筆資料,一共有10個頁面,目前要顯示第3頁。
NOT IN (  )  這個括弧裡面所存的資料,是前2頁的資料, 
NOT IN (  )  正表示著要剔除前2頁的資料,
select To 10 ,則是剔除前2頁資料後,取最前面的10筆,也就會抓到我們所謂的第3頁資料。


會如此自討苦吃部是沒有原因的,當所要查詢的資料量很龐大時,不管是使用 DataGrid 還是 GridView,
都會直接將所查詢到的大量資料回傳到 Web AP,然後再將資料整理成USER所要看的HTML傳到USER的
瀏覽器。此時,除了等待之外,就是把頁面關掉,或上YAHOO新聞看看政經八卦了。

透過 SQL 自己實作分頁,可以大幅縮短使用者檢視大量查詢結果的時間,蠻有助益的。





註一: 
如果是在 SQL 2000,可以透過 IDENTITY(int,1,1) 來自動產生序號
select IDENTITY(int,1,1) AS 'Row_Number',* 
into #tmp from common..comper  ORDER BY com_empno DESC

沒有留言:

張貼留言