批 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那样,改天我再测试一下是否方便.

另外,网上流传的很多分页存储过程,很多都是只适合单表查询或者

单个字段排序,   现实中很少单表查询和单字段排序的,基本上也是没什么用,个别适合多表查询的,存储过程里面又写得复杂得半死,分页效率用屁股想想就知道了