[ 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