方案一:(利用ID大于多少和SELECT TOP分页）
ALTER PROCEDURE [dbo].[selPagesByTop]
@tblName nvarchar(255), -- 表名
@priKeyName nvarchar(50), -- 主键列或标示列
@fldNames nvarchar(1000), -- 字段名,多个字段通过逗号分割
@PageSize int, -- 页尺寸
@PageIndex int, -- 页码
@OrderType nvarchar(200), -- 设置排序，'':没有排序要求 0：主键升序 1：主键降序 字符串：用户自定义排序规则
@strWhere nvarchar(2000), -- 查询条件 (注意: 不要加 where)
@strJoin nvarchar(1000) -- 连接表
AS
declare @strByPage nvarchar(4000) -- 分页查询语句
declare @strTotal nvarchar(1000) -- 统计符合条件的纪录数
declare @strNonResult nvarchar(1500) -- 返回空记录的语句
declare @strTmp nvarchar(100)
declare @strOrder nvarchar(200)
declare @rowcount int
set nocount on
if @OrderType=''
begin
set @strOrder = ''
set @strTmp=''
end
else if @OrderType = '0' -- 降序
begin
set @strTmp = @tblName + '.' + @priKeyName + '>(select max([' + @priKeyName + ']) from '
set @strOrder = ' order by ' + @tblName + '.' + @priKeyName + ' asc'
end
else if @OrderType = '1' -- 降序
begin
set @strTmp = @tblName + '.' + @priKeyName + '<(select min([' + @priKeyName + ']) from '
set @strOrder = ' order by ' + @tblName + '.' + @priKeyName + ' desc'
end
else -- 用户自定义排序规则
begin
set @strTmp = ''
set @strOrder = ' order by ' + @OrderType
end
set @strJoin = ' ' + @strJoin + ' '
set @strNonResult = 'select ' + @fldNames + ' from ' + @tblName + @strJoin + ' where 1=2'
if @strWhere = '' -- 如果没有额外的查询条件
begin
set @strTotal = N'select @rowcount = count(*) from ' + @tblname
end
else
begin
set @strTotal = N'select @rowcount = count(*) from ' + @tblname + ' where ' + @strWhere
end
-- 取得所有符合查询条件的记录数
if @PageIndex=1
begin
exec sp_executeSql @strTotal,N'@rowcount int output',@rowcount output
if @rowcount = 0
begin
exec sp_executeSql @strNonResult
return 0
end
end
else
set @rowcount=0
-- 执行查询，此时记录集不为空
-- 得到记录的页数，并调整页号，分页从1开始
if @PageIndex =1 -- 如果是第一页
begin
if @strWhere = ''
set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldNames + ' from ' + @tblName + @strJoin + @strOrder
else
set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldNames + ' from ' + @tblName + @strJoin + ' where ' + @strWhere + @strOrder
end
else -- 以后页
begin
if (@OrderType='0' or @OrderType='1') -- 按主键升序或降序
begin
if @strWhere = ''
set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldnames
+ ' from ' + @tblName
+ @strJoin
+ ' where ' + @strTmp
+ ' (select top ' + cast((@PageIndex-1) * @PageSize as varchar) + ' ' + @priKeyName
+ ' from ' + @tblName + @strOrder + ') as tmptbl)'
+ @strOrder
else
set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldnames
+ ' from ' + @tblName
+ @strJoin
+ ' where ' + @strTmp
+ ' (select top '+ cast((@PageIndex-1) * @PageSize as varchar) + ' ' + @priKeyName
+ ' from ' + @tblName + ' where ' + @strWhere + @strOrder + ') as tmptbl)'
+ ' and ' + @strWhere
+ @strOrder
end
else -- 没有排序规则或者用户自定义规则
begin
if @strWhere = ''
set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldnames
+ ' from ' + @tblName
+ @strJoin
+ ' where not exists (select * from '
+ ' (select top ' + cast((@PageIndex-1) * @PageSize as varchar) + ' * from '
+ @tblName + @strorder + ') as tmpTable '
+ ' where tmpTable.' + @priKeyName + ' = ' + @tblName +'.' + @priKeyName + ')'
+ @strorder
else
set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldnames
+ ' from ' + @tblName
+ @strJoin
+ ' where not exists (select * from '
+ ' (select top ' + cast((@PageIndex-1) * @PageSize as varchar) + ' * from '
+ @tblName + ' where ' + @strWhere + @strorder + ') as tmpTable '
+ ' where tmpTable.' + @priKeyName + ' = ' + @tblName + '.' + @priKeyName + ')'
+ ' and ' + @strWhere
+ @strorder
end
end
exec sp_executeSql @strByPage
return @rowcount
set nocount off
方案二:(利用SQL的游标存储过程分页)
ALTER procedure [dbo].[selPagesByCursor]
@PageIndex int,--第N页
@PageSize int,--每页行数
@sqlstr nvarchar(4000)
as
set nocount on
declare @P1 int,--P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
--select @rowcount as 总行数,ceiling(1.0*@rowcount/@pagesize) as 页数,@PageIndex as 当前页
set @PageIndex=(@PageIndex-1)*@PageSize+1
exec sp_cursorfetch @P1,16,@PageIndex,@PageSize
exec sp_cursorclose @P1
return @rowcount
set nocount off
方案三:(结合通用表达式进行分页)
ALTER procedure [dbo].[selTestTable]
@PageIndex int,
@PageSize int
as
Declare @rowcount numeric
Declare @intStart numeric
set nocount on
set @intStart=(@PageIndex-1)*@PageSize+1
if @intStart=1
SELECT @rowcount=count(ID) from TestTable where FirstName like '%aa%'
else
set @rowcount=0
;
WITH PartsCTE AS(Select ROW_NUMBER() OVER(order by ID) as row,
*
From TestTable where FirstName like '%aa%'
)
Select *
From PartsCTE A
where row between @intStart and @intStart+@PageSize-1
return @rowcount
set nocount off
方案二首次查询最快,因为另外两个方案还要统计记录总数.
后面的页由于不用统计总数,方案二反而最慢.
方案三和方案一速度一样,可能方案三占些微弱优势.
本人建议是:
首次用方案二查询,取得记录总数,后面的页面用另外的两个方案
posted on
