SELECT*FROM(
SELECTTOP页面容量*FROM(
SELECTTOP页面容量*当前页码*FROM
表WHERE条件ORDERBY字段AASC
)ASTEMPTABLE1ORDERBY字段ADESC
)ASTEMPTABLE2ORDERBY字段AASC
第一种方案:
两次top分页,原型如下:
SELECT*FROM(
SELECTTOP页面容量*FROM(
SELECTTOP页面容量*当前页码*FROM
表WHERE条件ORDERBY字段AASC
)ASTEMPTABLE1ORDERBY字段ADESC
)ASTEMPTABLE2ORDERBY字段AASC
弊病:1强制排序否则不能分页,虽然目前基本上查询表都要排序
2.排序字段不能有空值即null,否则分页结果不符实际情况
3.多次orderby速度会快吗,有待我进一步大数据量测试
基于上面的分页原理,我写了一个存储过程,有兴趣的看看,如下;
-----------------------------------------------------------------------------------------------------------
alterprocsp_pagination
@tbNamevarchar(200),--表名,可多表,逗号分隔
@tbFieldsvarchar(500)='*',--字段名,如果多表请带前缀
@whereStrvarchar(300)='',--where子句,可为空,不带where
@orderStrvarchar(300),--排序字段,可多个,要带desc或asc,不带orderby,必须,不能为空
--排序字段不能有空值,或者在where中排除空值或者用isnull函数解决
@needCoundbit=0,--是否需要得到纪录总数
@pageIndexint=0,--页索引
@pageSizeint=10,--页大小
@recordCountBIGINT=0output,--返回纪录总数
@pageCountint=0output--返回页总数
as
declare@sqlnvarchar(1300)--主sql语句
declare@orderStr2varchar(300)--orderby子句
set@orderStr=LOWER(@orderStr)
set@orderStr2=REPLACE(@orderStr,'desc','@a@')
set@orderStr2=REPLACE(@orderStr2,'asc','@d@')
set@orderStr2=REPLACE(@orderStr2,'@a@','asc')
set@orderStr2=REPLACE(@orderStr2,'@d@','desc')
set@orderStr='orderby'+@orderStr
set@orderStr2='orderby'+@orderStr2
if(@whereStrisnotnulland@whereStr!='')
set@whereStr='where'+@whereStr
else
set@whereStr=''
if(@needCound!=0or@pageIndex=0)--以下获得纪录总数
begin
DECLARE@RBIGINT
SET@sql=N'select@R=count(*)from'+@tbName
EXECSP_EXECUTESQL@SQL,N'@RBIGINTOUTPUT',@ROUTPUT
SET@recordCount=@R
set@pageCount=((@recordCount-1)/@pageSize)+1
end
if(@pageIndex<2)--如果是第一页
begin
set@pageIndex=1
set@sql='selecttop'+str(@pageSize)+''+@tbFields+'from'+@tbName+@whereStr+@orderStr;
end
else--其它页
begin
SET@sql='SELECT'+@tbFields+'FROM('
+'SELECTTOP'+STR(@pageSize)+''+@tbFields+'FROM('
+'selecttop'+STR(@pageSize*@pageIndex)+''+@tbFields+'FROM'
+@tbName+@whereStr+@orderStr+')asa'
+@orderStr2+')asb'+@orderStr
end
print@sql
EXECSP_EXECUTESQL@sql
--测试
declare@aBIGINT,@bBIGINT
execsp_pagination'orders','*','','orderidasc',1,5,10,@aoutput,@boutput
print@a
print@b
------------------------------------------------------------------------------------------------------
第二种方案:基于notin,原型如下
selecttop页大小*
fromtesttable
where(
idnotin
(selecttop页大小*页数idfrom表orderbyid)
)
orderbyid
弊病:1强制排序
2排序列必须是唯一列,否则分页情况不符实际
3.使用notin,速度慢,
第三种方案:基于max或者min,原型如下:
selecttop页大小*
fromtesttable
where(
id>(
selectmax(id)from(selecttop页大小*页数idfrom表
orderbyid)ast
)
)
orderbyid
弊病:1强制排序
2排序列必须是唯一列,否则分页情况不符实际
最后总结:
sqlserver分页就是烂,第二第三种方案基本上是淘汰掉了
,因为现在基本上什么表都是根据添加时间来排序,所以那两种方案
没有用,真亏作者也敢发布出来,
只有第一种方案还是稍微能用一下,但还是要复杂的拼sql语句,不方便,要通用于所有表有点难度,
象oracle就很方便了,基于rownum,传入一个sql查询语句,这个查询语句爱怎么写就怎么写,反正保证它得到一个结果集就行,不像sqlserver又是要求唯一健又是要求必须排序,把一个结果集颠来倒去,不慢才怪呢,
