yxhxj2006

/*查詢原理：需要查詢第N頁時，首先取到前N頁的所有數(shù)據(jù)，然后排除前N-1頁的數(shù)據(jù)，就是第N頁的數(shù)據(jù)*/  
create  PROCEDURE GetDataWithPage  
(   
       @pageIndex  int = 1,           -- 頁碼  
      @pageSize   int = 20,          -- 頁尺寸    
)  
as  
    begin  
     if @pageIndex < 1  
begin  
Set @pageIndex=1  
end                   
  
  
--如果是第一頁時要執(zhí)行的語句  
  if @PageIndex = 1  
      begin  
              select top ((@PageIndex)*@PageSize)   
               field1,field2,--查詢字段  
               fieldOrderby --排序字段，按什么字段分頁的字段，建議使用主鍵，或者唯一鍵  
               from tableName --查詢表名稱  
              where id>100 --查詢條件  
              order by fieldOrderby --排序字段，按什么字段分頁的字段，  
             select count(*) as Total from tableName where id>100--返回總記錄數(shù)  
    end  
  else    
         begin  
                 select top ((@PageIndex)*@PageSize) --取出前PageIndex頁的數(shù)據(jù)  
                            field1,field2,--查詢字段  
                            fieldOrderby --排序字段，按什么字段分頁的字段，建議使用主鍵，或者唯一鍵  
                            into #tempTable --插入臨時表  
                            from tableName --查詢表名稱  
                            where id>100 --查詢條件  
                           order by fieldOrderby --排序字段，按什么字段分頁的字段，  
                  -----取出前pageIndex頁數(shù)據(jù)插入臨時表  
  
                 ------排除前pageIndex-1頁的數(shù)據(jù)，取出第pageIndex的數(shù)據(jù)  
                  select top (@PageIndex) --  
                             field1,field2,--排序字段  
                            fieldOrderby --  
                           from #tempTable --從臨時表中取數(shù)據(jù)  
                          where fieldOrderby --  
                          not in (select  top ((@PageIndex-1)*@PageSize) fieldOrderby from #tempTable)  
                        ---- 從臨時表取出 pageIndex的數(shù)據(jù)   
                            ----可以根據(jù)升序或者降序把not in 改為 <min(fieldOrderby)或者>max(fieldOrderby)  
                 select count(*) as Total from tableName where id>100--返回總記錄數(shù)  
       end  
end  


2通過系統(tǒng)存儲過程進(jìn)行分頁查詢

[sql] view plaincopy
 /*  
 一共返回三個表第一個表為空 查詢字符串的條件中有 like ,in 可能出現(xiàn)問題，具體出現(xiàn)原因不明，第二個表包含總頁數(shù)，總行數(shù)，  
當(dāng)前頁第三個表包含查詢記錄  
  
*/  
create   procedure [dbo].[GetOnePageData]  
   @sqlstr nvarchar(4000), --查詢字符串  ，就是一般的查詢語句，不需要top關(guān)鍵字  
   @currentpage int, --第N頁  
   @pagesize int --每頁行數(shù)  
as  
   set nocount on  
   declare @P1 int, --P1是游標(biāo)的id  
   @rowcount int--  
    exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output  
   --定義與游標(biāo)和游標(biāo)選項(xiàng)相關(guān)聯(lián)的 SQL 語句，然后填充游標(biāo)。  
   --選擇總頁數(shù)，總行數(shù)，當(dāng)前頁  
    select ceiling(1.0*@rowcount/@pagesize) as TotalPages,@rowcount as TotalRows,@currentpage as CurPage  
    set @currentpage=(@currentpage-1)*@pagesize+1  
   --查詢記錄  
    exec sp_cursorfetch @P1,16,@currentpage,@pagesize --從游標(biāo)中提取一行或一個行塊。  
   --返回值  
    exec sp_cursorclose @P1--關(guān)閉并釋放游標(biāo)  
     set nocount off  
go  


3 通過新函數(shù)ROW_NUMBER()進(jìn)行分頁查詢

[sql] view plaincopy
/*  
適用于sql2005，據(jù)調(diào)查此方法限制最少，性能最佳  
返回兩個表，第一個表包含總行數(shù)，第二個表是查詢到的記錄  
--分頁查詢的原理：  
--1.先將預(yù)分頁內(nèi)容按照排序條件加上自增列導(dǎo)入到臨時表中（或表變量）  
--2.針對臨時表操作，找到相應(yīng)的N頁對應(yīng)的自增列編碼范圍  
--3.根據(jù)第N頁對應(yīng)的自增列編碼范圍，查找第N頁內(nèi)容  
   
--需要注意的是：  
--第一是添加自增列，確定行號  
--第二縮減中間處理過程的操作數(shù)據(jù)量  
  
*/  
  
create  proc [dbo].[GetOnePageData]--//  
(  
    @page int,  
    @pagesize int  
)  
as  
begin      
        select top  (@pagesize*@page)  ROW_NUMBER() --可以減少處理的數(shù)據(jù)，加快速度  
         OVER(ORDER BY NodeID) as rowNum,--按什么字段分頁的字段，不需要唯一性  
        NodeID,NodeName,ControlIP --查詢字段  
        into #temp --插入臨時表  
        from TableName   --  查詢表名稱   
        where NodeID>100 --查詢條件  
         
       select count(*) from #temp--總記錄條數(shù)  
       select * from #temp where rowNum>@pagesize*(@page-1) and rowNum<=@pagesize*@page--  
end   

  綜述，如果需要通用的存儲過程，可以把第一或者第三種分頁方法進(jìn)行改寫，在數(shù)據(jù)庫拼字符串，但性能會差好多。這或許就是通用和性能之間的矛盾吧。