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
第一种方案:
两次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 ', ' @a@ ')
set @orderStr2 = REPLACE(@orderStr2, ' asc ', ' @d@ ')
set @orderStr2 = REPLACE(@orderStr2, ' @a@ ', ' asc ')
set @orderStr2 = REPLACE(@orderStr2, ' @d@ ', ' 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 '+@tbName
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 ( '
