批 sqlserver 2000 三種分頁方案

先說最流行的那種

第一種方案:

兩次top分頁,原型如下:  

SELECT   *   FROM   (

SELECT   TOP   頁面容量   *   FROM   (

SELECT   TOP   頁面容量*目前頁碼   *   FROM  

表   WHERE   條件   ORDER   BY   字段A   ASC

)   AS   TEMPTABLE1   ORDER   BY   字段A   DESC

)   AS   TEMPTABLE2   ORDER   BY   字段A   ASC

弊病:1   強制排序   否則不能分頁,雖然目前基本上查詢表都要排序

2.   排序字段不能有空值即null,否則分頁結果不符實際情況

3.   多次order   by   速度會快嗎,有待我進一步大資料量測試

基于上面的分頁原理,我寫了一個存儲過程,有興趣的看看,如下;

-----------------------------------------------------------------------------------------------------------

alter   proc   sp_pagination

@tbName   varchar(200),   --表名,可多表,逗号分隔

@tbFields   varchar(500)= '* ',--字段名,如果多表請帶字首

@whereStr   varchar(300)= ' ',   --where子句,可為空,不帶where

@orderStr   varchar(300),   --排序字段,可多個,要帶desc或asc,不帶order   by,必須,不能為空

--排序字段不能有空值,或者在where中排除空值或者用isnull函數解決

@needCound   bit   =   0,   --是否需要得到紀錄總數

@pageIndex   int   =0,   --頁索引

@pageSize   int=10,   --頁大小

@recordCount   BIGINT   =0   output,   --傳回紀錄總數

@pageCount   int   =0   output   --傳回頁總數

as

declare   @sql   nvarchar(1300)   --主sql語句

declare   @orderStr2   varchar(300)   --order   by子句

set   @orderStr   =   LOWER(@orderStr)

set   @orderStr2   =   REPLACE(@orderStr, '   desc ', '   @[email protected] ')

set   @orderStr2   =   REPLACE(@orderStr2, '   asc ', '   @[email protected] ')

set   @orderStr2   =   REPLACE(@orderStr2, '   @[email protected] ', '   asc ')

set   @orderStr2   =   REPLACE(@orderStr2, '   @[email protected] ', '   desc ')

set   @orderStr   =   '   order   by   '   +   @orderStr

set   @orderStr2   =   '   order   by   '   +   @orderStr2

if(@whereStr   is   not   null   and   @whereStr   != ' '   )

set   @whereStr   =   '   where   '   +   @whereStr

else

set   @whereStr   =   ' '

if(@needCound   !=   0   or   @pageIndex   =   0)   --以下獲得紀錄總數

begin

DECLARE   @R   BIGINT

SET   @sql=   N 'select   @R=count(*)   from   '[email protected]

EXEC   SP_EXECUTESQL   @SQL,N '@R   BIGINT   OUTPUT ',@R   OUTPUT

SET   @recordCount   =   @R

set   @pageCount   =   ((@recordCount-1)/@pageSize)+1  

end

if(@pageIndex <2)   --如果是第一頁

begin

set   @pageIndex   =   1

set   @sql= 'select   top   '+   str(@pageSize)   +   '   '+   @tbFields   +   '   from   '   +   @tbName   +   @whereStr   +   @orderStr;

end

else   --其它頁

begin

SET   @sql=   'SELECT   '   +   @tbFields   +   '   FROM   ( '

+   'SELECT   TOP   '   +   STR(@pageSize)   +   '   '+   @tbFields   +   '   FROM   ( '

+   'select   top   '   +   STR(@pageSize*@pageIndex)   +   '   '+   @tbFields   +   '   FROM   '

+   @tbName   +   @whereStr   +   @orderStr   +   ')   as   a '

+   @orderStr2   +   ')   as   b '   +   @orderStr

end

print   @sql  

EXEC   SP_EXECUTESQL   @sql  

--測試

declare   @a   BIGINT,@b   BIGINT

exec   sp_pagination   'orders ', '* ', ' ', 'orderid   asc ',1,5,10,@a   output,@b   output

print   @a

print   @b

------------------------------------------------------------------------------------------------------

第二種方案:基于not   in   ,原型如下

select   top   頁大小   *

from   testtable

where   (

id   not   in

(select   top   頁大小*頁數   id   from   表   order   by   id)

)

order   by   id

弊病:1   強制排序

2   排序列必須是唯一列,否則分頁情況不符實際

3.   使用not   in,速度慢,

第三種方案:   基于max   或者   min   ,原型如下:

select   top   頁大小   *

from   testtable

where   (

id   >   (  

select   max(id   )   from   (   select   top   頁大小*頁數   id   from   表

order   by   id   )   as   t

)

)  

order   by   id

弊病:1   強制排序

2   排序列必須是唯一列,否則分頁情況不符實際

最後總結:

sqlserver   分頁就是爛,第二第三種方案基本上是淘汰掉了

,因為現在基本上什麼表都是根據添加時間來排序,是以那兩種方案

沒有用,真虧作者也敢釋出出來,

隻有第一種方案還是稍微能用一下,但還是要複雜的拼sql   語句,不友善,要通用于所有表有點難度,

象oracle   就很友善了,基于rownum,傳入一個sql   查詢語句,這個查詢語句愛怎麼寫就怎麼寫,反正保證它得到一個結果集就行,不像sqlserver又是要求唯一健又是要求必須排序,把一個結果集颠來倒去,不慢才怪呢,

題外話:

sqlserver   2005已經支援行号了,就象oracle那樣,改天我再測試一下是否友善.

另外,網上流傳的很多分頁存儲過程,很多都是隻适合單表查詢或者

單個字段排序,   現實中很少單表查詢和單字段排序的,基本上也是沒什麼用,個别适合多表查詢的,存儲過程裡面又寫得複雜得半死,分頁效率用屁股想想就知道了