網路工作室暑假後第三次培訓資料（幾種SQL分頁的總結）整理

在實際開發的過程中如果記錄數非常的龐大，如果直接用SQL語句查詢並填充到DataTable中，將是一件非常恐怖的事情。而且對網站效能，伺服器效能消耗很大。

兩個常犯的錯誤：

1）在現實資料時，查詢時會將所有的滿足條件的資料全部填充到DataTable中，然後在程式中根據條件顯示其中的一部分資料。

2）在統計資料時，在擷取合格記錄條數時也是通過將所有滿足條件的資料全部填充到DataTable中，然後通過DataTable執行個體Rows.Count屬性來擷取記錄條數。

這樣做的結果就是效率極低，如果資料量太大，可能造成自己需要的資料長久的無法顯示，所以顯示資料是應該使用分頁查詢。分頁查詢就是每次只返回所需要的資料，而不用每次都從資料庫中把資料全部提出來，這樣可以降低程式與資料庫之間的資料傳送量，並且還可以提高程式的效能。

一般來說，在資料量大的情況下要分頁顯示，這樣決定返回的查詢結果集的參數有兩個：當前顯示的頁數pageIndex和每頁顯示的記錄條數size。

所使用的資料表如：表中的id是主鍵。

650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131228/12360JG5-0.png" />

下面我們按照id正序排列查詢所有人的資訊，SQL語句如下：

 

  
select * from T_Person orderby id ASC; 
 
現在我們對資料進行分頁，分頁規則就是，頁容量5條資料，那麼我們完成第1頁的資料查詢可以使用SQL語句是：
  

   
selecttop 5 * from T_Person orderby id asc; 
  
這樣我們就可以取出第1頁所要顯示的5條資料。但是我們應該怎麼樣編寫SQL語句才可以顯示第2,3,4......頁的資料呢？
如果說我們第1頁取出的資料時第1-5條記錄，那麼第2頁的資料就應該是6-10的記錄。我們應該怎麼做到呢？有兩種方法：第一種就是一次性的將所有的資料都提取出填充到DataTable中，然後在for迴圈中通過i從5開始，並且i小於10這種方法顯示資料，這種方法的缺點前面已經介紹。第二種方法就是在資料庫中對資料進行過濾，這個時候SQL語句中的 not in 就可以很好地排上用場。
那麼怎麼使用not in將第2頁的資料顯示出來呢，SQL語句如下：
   

    
selecttop 5 * from T_Person where id notin
    
( 
    
selecttop 5 id from T_Person orderby id asc
    
)orderby id asc; 
   
在這裡使用了一個子查詢先將第1頁的資料編號顯示出來，然後使用not in 將1-5的記錄從資料中排出，顯示的就是6-10 的資料記錄。
因為資料id是從1開始，所以id為1-5的記錄顯示在第1頁，id為6-10的記錄在第2頁顯示，id為11-15的記錄在第3頁顯示，依此類推第n頁的資料的SQL語句是：n為定義顯示資料的第幾頁，
    

     
selecttop 5 * from T_Person where id notin
     
( 
     
selecttop (n-1)5 id from T_Person orderby id asc
     
)orderby id asc;
    
這樣就可以根據參數n顯示第幾頁的資料。
還有一個比較重要的知識點就是如何計算資料頁分頁的總數，如果現在有20條資料，如果每頁5條資料，很明顯就是分4頁。但是如果記錄數是21條，這個時候很明顯應該分5頁。有一個公式，假如總共有m條資料，每頁顯示n條資料m，n都大於0），那麼需要顯示所有記錄的頁數page為：page=m%n）==0？m/n）：m/n+1）；
第二種分頁的方法：
這裡需要使用SQL中的ROW_NUMBER函數，該函數的作用就是在返回的記錄集合內為每一條記錄標上順序編號。
因為我們要對資料庫進行刪除操作的話，資料庫表中的id的值可能是不連續的。因為在上面的方法中，我們主要是使用id來排序，不需要太多的操作，但是下面的方法，就需要有一個連續的id值來查詢資料。
先看一個SQL語句：
    

     
select * from
     
( 
     
select * from T_Person 
     
)as a 
     
where id>5 and id<=10 
    
這樣的話，我們仍然能夠按照要求取出相應頁數的資料。我們只需要稍微改變一下，SQL語句如下：n為顯示資料的頁碼
     

      
select * from
      
( 
      
select * from T_Person 
      
)as a 
      
where id>(n-1)*5 and id<=n*5
     
這個時候我們就可以根據參數n來選出相應頁數的資料，但是我們可以很快的發現，這個分頁對id的連續有很大的依賴性，所以，我們在對資料進行刪除操作的時候，資料庫中的id很可能是不連續的，所以在資料庫中進行分頁的時候，就需要對資料進行編號，這個時候就要使用SQl中的ROW_NUMBER函數，
使用ROW_NUMBER函數查詢的SQl語句和顯示結果如下：
650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131228/12360L308-1.png" />
很容易的我們發現在id的前面有了一個按照id正序排列的rl的列。
所以，完整的 SQL分頁語句為：n為我們傳入的分頁的頁碼，預設的每頁顯示的資料位元5條
    

     
select * from
     
( 
     
select * from T_Person 
     
)as a 
     
where id>(n-1)*5 and id<=n*5 
    
這樣，我們就可以建立相應的分頁預存程序，在資料庫中對資料進行分頁，然後供程式進行調用。
最後分享一個，在網上看到的一個比較好的分頁預存程序：

     

      
createPROCEDURE GetPageData 
      
( 
      
@TableName varchar(30),--表名稱
      
@IDName varchar(20),--表主鍵名稱
      
@PageIndex int,--當前頁數 
      
@PageSize int--每頁大小 
      
) 
      
AS
      
IF @PageIndex > 0 
      
BEGIN
      
set nocount on
      
DECLARE @PageLowerBound int,@StartID int,@sql nvarchar(225) 
      
SET @PageLowerBound = @PageSize * (@PageIndex-1) 
      
   IF @PageLowerBound<1 
      
SET @PageLowerBound=1 
      
SET ROWCOUNT @PageLowerBound 
      
SET @sql=N'SELECT @StartID = ['+@IDName+'] FROM '+@TableName+' ORDER BY '+@IDName 
      
exec sp_executesql @sql,N'@StartID int output',@StartID output
      
SET ROWCOUNT 0 
      
SET @sql='select top '+str(@PageSize) +' * from '+@TableName+' where ['+@IDName+']>='+ str(@StartID) +' ORDER BY ['+@IDName+'] '
      
EXEC(@sql) 
      
set nocount off
      
END
     
原創作品，允許轉載，轉載時請務必以超連結形式標明文章 原始出處 、作者資訊和本聲明。否則將追究法律責任。
650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131228/12360IK2-2.png" />

本文出自 “強子的專欄” 部落格，請務必保留此出處http://yisuowushinian.blog.51cto.com/4241271/1034882