[ MSSQL ]分页排序存储过程

第一种方案,我们先取集集合OB,这可以用一个使用SELECT TOP 并使用升级排列的语句完成

然后对OB结果集进行倒序排序,再用"SELECT TOP 每页记录数"倒序 的方式取得目标集合AB,下面是存储过程

 

create PROCEDURE dbo.GetPagingData

(

@tablename varchar(100),--表名或视图表

@fieldlist varchar(4000)='*',--欲选择字段列表

@orderfield varchar(100),--排序字段

@keyfield varchar(100),--主键

@pageindex int,--页号,从0开始

@pagesize int=20,--页尺寸

@strwhere varchar(4000),--条件

@ordertype bit=1--排序,1,降序,0,升序

)

AS

BEGIN

SET NOCOUNT ON

declare @sqlstr varchar(6000)

--处理SQL中危险字符,并且将条件处理成易嵌入的形式

set @sqlstr='declare @Rcount int;'

set @sqlstr=@sqlstr+'set @rcount=(select count('+@keyfield+') from '+@tablename+' where '+@strWhere+');'

set @strwhere=replace(@strwhere,'''','''''')

set @strwhere=replace(@strwhere,'--','')

set @strwhere=replace(@strwhere,';','')

set @sqlstr=@sqlstr+'declare @Rnum int;'

set @sqlstr=@sqlstr+'set @rnum=@rcount-'+cast(@pagesize as varchar)+'*'+cast(@pageindex as varchar)+';'

set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);'

if @ordertype=1

begin

set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@Pagesize as varchar)+' '+@fieldlist+' from (select top 100

percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@tablename+' where '+@strwhere+'

order by '+@orderfield+' asc) as b order by paymoney desc) as a order by '+@orderfield+' desc '';'

end

else

begin

set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@Pagesize as varchar)+' '+@fieldlist+' from (select top 100

percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@tablename+' where '+@strwhere+'

order by '+@orderfield+' desc) as b order by paymoney asc) as a order by '+@orderfield+' asc '';'

end

set @sqlstr=@sqlstr+'if @Rcount>0 begin execute(@sqlstr) end'

--print @sqlstr

execute(@sqlstr)

END

 

 

在上面的代码中,还处理了没有符合条件结果的情况

第二种方案的思想是这样的,先用倒序的SELECT TOP (页序号+1)*页尺寸的方法取得AE结果集,再从AE结果集中用NOT IN 的方法排除掉用SELECT TOP 页序号*页尺寸的方法取得的结果集,最后,对目标结果集执行倒序,下面是实现的代码

 

View Code

create PROCEDURE dbo.GetPagingRecord

(

@tablename varchar(100),--表名或视图表

@fieldlist varchar(4000)='*',--欲选择字段列表

@orderfield varchar(100),--排序字段

@keyfield varchar(100),--主键

@pageindex int,--页号,从0开始

@pagesize int=20,--页尺寸

@strwhere varchar(4000),--条件

@ordertype bit=1--排序,1,降序,0,升序

)

AS

SET NOCOUNT ON

declare @sqlstr varchar(6000)

--处理SQL中危险字符,并且将条件处理成易嵌入的形式

set @strwhere=replace(@strwhere,'''','''''')

set @strwhere=replace(@strwhere,'--','')

set @strwhere=replace(@strwhere,';','')

set @sqlstr='declare @CurPageNum int;'

set @sqlstr=@sqlstr+'declare @nextpagenum int;'

set @sqlstr=@sqlstr+'set @curpagenum='+cast(@PageIndex as varchar)+'*'+cast(@Pagesize as varchar)+';'

set @sqlstr=@sqlstr+'set @nextpagenum='+cast(@PageIndex+1 as varchar)+'*'+cast(@Pagesize as varchar)+';'

set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);'

if @ordertype=1

begin

set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from

'+@tablename+' where '+@strwhere+' order by '+@orderfield+' desc ) as a where '+@keyfield+' not in (

select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'

order by '+@orderfield+' desc) order by '+@orderfield+' desc'';'

end

else

begin

set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from

'+@tablename+' where '+@strwhere+' order by '+@orderfield+' asc ) as a where '+@keyfield+' not in (

select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'

order by '+@orderfield+' asc) order by '+@orderfield+' asc'';'

end

set @sqlstr=@sqlstr+'execute( @sqlstr)'

--print @sqlstr

execute(@sqlstr)

 

需要注意的是,如果要避免SQL注入式攻击,请注意处理像分号,双减号,单引号这些在SQL中有特殊含义的字符

至于上面两个存储过程哪个性能更好,那就取决于是对一个倒序的结果集再进入一次反序排列好呢,还是用NOT IN从一个大的结果集移除一个小的结果集好

第三种方案

成熟的分类过程

View Code

create PROCEDURE CMS_SearchGetDataByPage
@tblName varchar(255), -- 表名
@fldNames varchar(1000), -- 选择的字段列表以,分隔
@selectOrderFldName varchar(500), -- 排序字段以,分隔（不能含keyFldName指定的字段，可为空）
@orderFldDesc varchar(500), -- 排序字段及排序方向，如addDate desc,id desc（排序字段需通过selectOrderFldName指定，可为空）
@keyFldName varchar(255), -- 主键字段
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@strWhere varchar(1000) = '' -- 查询条件(注意: 不要加where)
AS
declare @strWhereA varchar(1200) -- 临时变量，给sqlwhere加where
declare @strOrderA varchar(2000) -- 第一次排序类型
declare @strOrderB varchar(2000) -- 第二次排序类型
declare @strSqlA varchar(4000) -- 第一次选出
declare @strSqlB varchar(8000) -- 第二次选出
declare @strSQL varchar(8000) -- 最后选出
/* 条件*/
if @strWhere != ''
set @strWhereA = ' where ' + @strWhere
else
set @strWhereA = ''

/* 选择字段列表*/
if @fldNames is null or rtrim(@fldNames) = ''
set @fldNames = '*'

/* 排序字段列表*/
if not(@selectOrderFldName is null or rtrim(@selectOrderFldName) = '')
if rtrim(@selectOrderFldName) = 'id'
set @selectOrderFldName = ''
else
set @selectOrderFldName = ',' + @selectOrderFldName

/* 构建order，按指定方式排序*/
if @orderFldDesc is null or rtrim(@orderFldDesc) = ''
set @orderFldDesc = ' order by id desc'
else
set @orderFldDesc = ' order by ' + @orderFldDesc
set @strOrderA = UPPER(@orderFldDesc)
set @strOrderB = replace(@strOrderA,'DESC','DESC1')
set @strOrderB = replace(@strOrderB,'ASC','DESC')
set @strOrderB = replace(@strOrderB,'DESC1','ASC')

/* 第一页*/
if @PageIndex = 1
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldNames + ' from [' + @tblName + '] with(nolock)' + @strWhereA + ' ' + @strOrderA
else
begin
--取得总记录数
declare @sql nvarchar(500)
declare @maxCount int
declare @maxPage int
declare @tempRowCount int
set @sql ='select @maxCount = count('+@keyFldName+') from [' + @tblName + ']' + @strWhereA
exec sp_executesql @sql,N'@maxCount int output',@maxCount output
set @maxPage = @maxCount / @PageSize
if(@maxCount % @PageSize > 0)
set @maxPage = @maxPage + 1
/* 最后一页*/
if @PageIndex >= @maxPage
begin
set @PageIndex = @maxPage
set @strSqlA = char(13) + '(select top '+str(@maxCount % @PageSize)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a with(nolock) ' + @strWhereA + @strOrderB + ' )' + char(13)
set @strSqlB = char(13) + '(select ' + @keyFldName +' from ' + @strSqlA + ' as b )' + char(13)
set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA + char(13)
end
else
begin
/* 不是第一页，也不是最后一页*/
if(@PageIndex <= @maxPage / 2)
begin
--前半数的页
set @tempRowCount = @PageIndex * @PageSize
/* 构建SQL，本分页算法的目的是为了实现高效的非主键排序的分页。by tony */
/* 1、先按指定字段+主键字段按降序选出perPage*pageNum条记录*/
set @strSqlA = char(13) + '(select top '+str(@tempRowCount)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a with(nolock) ' + @strWhereA + @strOrderA + ' )' + char(13)
/* 2、再从选出的记录中按升序选出perPage条记录*/
set @strSqlB = char(13) + '(select top '+str(@PageSize)+' ' + @keyFldName + ' from ' + @strSqlA + ' as b ' + @strOrderB + ' )' + char(13)
/* 3、从数据库中选出主键在第二次选出的记录中的记录，按降序排列，分页完成*/
set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA
end
else
begin
--后半数的页
set @tempRowCount = @maxCount - (@PageIndex -1) * @PageSize
/* 构建SQL，本分页算法的目的是为了实现高效的非主键排序的分页。by tony */
/* 1、先按指定字段+主键字段按降序选出perPage*pageNum条记录*/
set @strSqlA = char(13) + '(select top '+str(@tempRowCount)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a with(nolock) ' + @strWhereA + @strOrderB + ' )' + char(13)
/* 2、再从选出的记录中按升序选出perPage条记录*/
set @strSqlB = char(13) + '(select top '+str(@PageSize)+' ' + @keyFldName + ' from ' + @strSqlA + ' as b ' + @strOrderA + ' )' + char(13)
/* 3、从数据库中选出主键在第二次选出的记录中的记录，按降序排列，分页完成*/
set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA
end
end
end

set nocount on

/*print @strSQL*/ --显示SQL
exec (@strSQL)
set nocount off
RETURN

第四种方案

View Code

CREATE PROC SP_PageList
@tbname sysname, --要分页显示的表名
@FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
--用于指定排序顺序
@Where nvarchar(1000)='', --查询条件
@RecordCount int OUTPUT, --总记录数
@PageCount int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000)
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN
END

--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
RETURN
END

--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'

--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql=N'SELECT @PageCount=COUNT(*)'
+N' FROM '+@tbname
+N' '+@Where
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
SET @RecordCount = @PageCount
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END

--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize

--第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder)
ELSE
BEGIN
SELECT @PageCurrent=@TopN1,
@sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN
+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey
+N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql,
N'@n int,@s nvarchar(4000) OUTPUT',
@PageCurrent,@sql OUTPUT
SET ROWCOUNT 0
IF @sql=N''
EXEC(N'SELECT TOP 0'
+N' '+@FieldShow
+N' FROM '+@tbname)
ELSE
BEGIN
SET @sql=STUFF(@sql,1,1,N'')
--执行查询
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' WHERE '+@FieldKey
+N' IN('+@sql
+N') '+@FieldOrder)
END
END
GO

转载于:https://www.cnblogs.com/samsonhuang/articles/2092839.html