贡献一个我修改过后可以支持任意字段排序多表关联的分页存储过程

[caoshangfei]

大家有什么问题，可以到我的博客与我进行交流。

以下脚本注释以我开发的新书城网上书店（www.newbooks.com.cn）为例进行注释。
本存储过程分页的主要思路是，排序的同时引入主键字段，如果排序字段值重复的时候，来利用排序字段值相等，但是主键不相等的条件来取记录，具体以price字段升序排序为例进行讲解：
1.先取得(@PageIndex-1)*@PageSize条记录中的price字段和主键字段。这些记录的顺序按照price升序和主键升序。
相关sql伪代码为：select top (@PageIndex-1)*@PageSize tblbooks.price,tblbooks.bookid from tblbooks inner join tblbooktypes on tblbooks.bookid=tblbooktypes.bookid
2.从上面的记录中取得top 1记录，top 1记录按照price降序和主键降序。其实就是取得上面记录中的最后一条记录，将这条记录的price字段和主键字段的值赋值给2个临时变量。
相关sql伪代码为:
select top 1 @orderFldValue=@orderFldName,@keyFldValue=@fldName from (select top (@PageIndex-1)*@PageSize tblbooks.price,tblbooks.bookid from tblbooks inner join tblbooktypes on tblbooks.bookid=tblbooktypes.bookid)
3.我们取得上面2个值后，就可以把这2个值作为条件，来取得我们的分页数据了。思路就是top 20 ... where price>取出来的price or (price=取出来的price and 主键bookid>取出来的主键值)
相关sql伪代码为：
select top 20 @listFldName from @tblName where @strWhere and (price>@orderFldValue or (price=@orderFldValue and bookid>@keyFldValue))
从而我们取出了分页数据。
调用的代码如下：
exec GetRecordFromPage "tblbooks inner join tblbooktypes on tblbooks.bookid=tblbooktypes.bookid","tblbooks.bookid","tblbooks.bookid,tblbooks.bookname,tblbooks.price",
"tblbooks.price","",20,3,0," tblbooktypes.typecode like '0.1.20%'"
本存储过程sql语句如下：
/*
参数说明: @tblName 需要查询的表名。如图书表tblbooks 。如果是多表（图书表关联图书分类表）则写成：tblbooks inner join tblbooktypes on tblbooks.bookid=tblbooktypes.bookid
@fldName 主键字段名 bookid
@listFldName 需要查询的字段。如：书名(tblbooks.bookname)、作者(tblbooks.author)、价格(tblbooks.price)
@orderFldName 需要排序的字段。 如(tblbooks.price) 我们以非主键且有重复字段价格字段进行排序
@orderFldType 需要排序的字段的类型。 因为price这段类型为float，所以我们这里设置值为"float"。
@PageSize 每页记录数
@PageIndex 要获取的页码
@OrderType 排序类型, 0 - 升序, 1 - 降序
@strWhere 查询条件 (注意: 不要加 where)
作者：草上飞
Q Q:1469886
说明：本存储过程为在优化新书城网上书店（www.newbooks.com.cn）的分页时候整理出来的。本存储过程可以任意转载，但在转载过程中请保留以上信息。谢谢！
利用该存储过程达到了根据价格、销量、出版日期等字段快速排序的效果。
如对该存储过程有疑问，请与本人联系。
*/


CREATE PROCEDURE GetRecordFromPage
@tblName varchar(500),
@fldName varchar(50),
@listFldName varchar(255),
@orderFldName varchar(50),
@orderFldType varchar(50),
@PageSize int = 10,
@PageIndex int = 1,
@OrderType bit = 0,
@strWhere varchar(2000) = ''
AS

declare @strSQL nvarchar(4000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(500) -- 排序类型
declare @strOrder2 varchar(500) --
declare @orderFldValue nvarchar(100) --排序字段对应的值
declare @keyFldValue nvarchar(100) --主键字段对应的值 add
declare @operator char(1) --add by caoy
declare @tempValueSql varchar(100)
declare @strOrderby varchar(5)
if (@orderFldType='float')
set @tempValueSql='cast(@orderFldValue as float)'
else
set @tempValueSql='@orderFldValue'

--获取表明 。
declare @tablename varchar(20)
if charindex('.',@orderFldName)>1
set @tablename=left(@orderFldName,charindex('.',@orderFldName)-1)
else
set @tablename=@orderFldName
if @OrderType != 0
begin
set @operator='<'
set @strOrderby=' desc'
set @strOrder2=' asc'
end
else
begin
set @operator='>'
set @strOrderby=' asc'
set @strOrder2=' desc'
end
set @strOrder=' order by '+ @orderFldName+@strOrderby
if @fldName!=@orderFldName --如果排序字段不是主键字段，则增加主键排序
set @strOrder=@strOrder+','+@fldName+@strOrderby
--先得到orderFldValue和keyValue
set @strSQL='select top 1 @orderFldValue=convert(nvarchar(100),'+@orderFldName+',20)' /***注意，如果需要排序的字段的值长度超过Nvarchar(100)，请修改此处***********/
if @fldName!=@orderFldName
set @strSQL=@strSQL+',@keyFldValue='+@fldName
else
set @strSQL=@strSQL+',@keyFldValue=1'
set @strSQL=@strSQL+' from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
+ @orderFldName
if @fldName!=@orderFldName --add by caoy
set @strSQL=@strSQL+','+@fldName
set @strSQL=@strSQL+ ' from ' + @tblName + ''
if @strWhere != ''
set @strSQL=@strSQL+ ' where '+@strWhere
set @strSQL=@strSQL+ @strOrder + ') as '+@tablename+' order by ' + @orderFldName +@strOrder2
if @fldName!=@orderFldName --add by caoy
set @strSQL=@strSQL+',' + @fldName +@strOrder2
--print @strSQL
exec sp_executesql @strSQL,N'@orderFldValue nvarchar(100) output,@keyFldValue nvarchar(100) output',@orderFldValue output,@keyFldValue output /***注意，如果需要排序的字段的值长度超过Nvarchar(100)，请修改此处***********/
--得到排序字段值和主键值结束



if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'

set @strSQL = 'select top ' + str(@PageSize) + ' '+ @listFldName+' from '
+ @tblName + '' + @strTmp + ' ' + @strOrder
exec (@strSQL)
end
else
begin
--取得top数据并返回
set @strSQL = N'select top ' + str(@PageSize) +' ' + @listFldName+' from '
+ @tblName + ' where ('+@orderFldName+@operator+@tempValueSql+' and @keyFldValue=@keyFldValue'
if @fldName!=@orderFldName --add by caoy
set @strSQL=@strSQL+ ' or ('+@orderFldName+'='+@tempValueSql+' and '+@fldName+@operator+'@keyFldValue)) and (1=1'

if @strWhere != ''
set @strSQL=@strSQL+' and ' + @strWhere
set @strSQL=@strSQL+ ')'+@strOrder
if @fldName=@orderFldName
set @keyFldValue=1
--print @strSQL
exec sp_executesql @strSQL,N'@orderFldValue nvarchar(100),@keyFldValue nvarchar(100)',@orderFldValue,@keyFldValue


end

SET QUOTED_IDENTIFIER OFF
GO

[Even713]

mark

[BoyHaXin]

UP

[fcuandy]

自己用这个够了.几年前写的了.

CREATE  PROCEDURE SP_Page

  @TB        		VARCHAR(50), 
  @COL      		VARCHAR(50),
  @ORDERBY 		BIT,       
  @COLLIST    		VARCHAR(800),
  @PAGESIZE  		INT,     
  @PAGE      		INT,  
  @CONDITION  		VARCHAR(800),
  @RecPages 		INT,
  @RecCount		INT OUTPUT,
  @PAGES    		INT OUTPUT,
  @OUTSQL		NVARCHAR(4000) OUTPUT

AS

DECLARE @SQL NVARCHAR(4000)
DECLARE @WHERE1 VARCHAR(800)
DECLARE @WHERE2 VARCHAR(800)

IF @CONDITION IS NULL OR  RTRIM(@CONDITION) = ''
	BEGIN
  		SET @WHERE1=' WHERE '
  		SET @WHERE2='  '
	END
ELSE
	BEGIN
  		SET @WHERE1=' WHERE ('+@CONDITION+') AND '
  		SET @WHERE2=' WHERE ('+@CONDITION+') '
	END

SET @SQL = 'SELECT @RecCount=COUNT(*),@PAGES=CEILING((+0.0)/'+CAST(@PAGESIZE AS VARCHAR)+') FROM '+@TB+@WHERE2

IF @RecPages = 0
	EXEC SP_EXECUTESQL @SQL,N'@PAGES INT OUTPUT,@RecCount INT OUTPUT',@PAGES OUTPUT,@RecCount OUTPUT
ELSE
	SELECT @PAGES = @RecPages

IF @ORDERBY=0
  SET @SQL=	'SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'>(SELECT MAX('+@COL+') '+' FROM (SELECT TOP '+CAST(@PAGESIZE*(@PAGE-1) AS VARCHAR)+' '+
           @COL+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+') t) ORDER BY '+@COL
ELSE
  SET @SQL='SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'<(SELECT MIN('+@COL+') '+' FROM (SELECT TOP '+CAST(@PAGESIZE*(@PAGE-1) AS VARCHAR)+' '+
           @COL+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+' DESC) t) ORDER BY '+@COL+' DESC'

IF @PAGE=1
  SET @SQL='SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+CASE @ORDERBY WHEN 0 THEN '' ELSE ' DESC' END

SET @OUTSQL = @SQL

EXEC(@SQL)
GO

[fcuandy]

楼主过程是多表的.

多表的分两种情况.
1,某一表处理过滤条件.其它表,只用作连表显示.
这种情况,是最简单的,考滤性能问题, 建议还是针对单表处理.所有处理完后再连表.

2,几个表共同进行条件过滤,并且几个表之前相互影响--连接条件.
这种情况比较复杂,要想写通用的分页存储过程不太可能,除非把所有的业务逻辑全封在里面,如果这样做,估计几千行脚本都写不完.即使写了,也只是针对某个系统的业务逻辑的.
这种建议针对性的写存储过程.

[CeShenBeiWang]

学习！

[caoshangfei]

to fcuandy.不知道你这个存储过程是否有考虑你排序的字段在数据库中有重复值时候的情况？
我的这个存储过程能处理这种情况。
大家有啥想法，欢迎提出来啊。要是版主在的话，麻烦把我的帖子编辑一下，改成sql code模式。谢谢！

[fcuandy]

你的写的好,呵呵. 我的是自己用的,够用就好.

真正公司系统上要用的东西,数据库端的东西,我以安全,稳定,效率为三大首要因素. 通用是其次.当然,前提是实现功能,功能不能实现,所有都是白搭.

所以我不会写这样的东西的.

[littlelam]

支持，用这个发布，才不会乱

[caoshangfei]

哦。没发现有这个。不能修改了。

[caoshangfei]

散分了。。。

[Atai-Lu]

我认为，楼主写的比fcuandy写的好用些，但是却比fcuandy 更复杂了点，没有fcuandy 的那么直观，或许是因为没有用sql code的原因吧？呵呵！
要我在这两者之间选择我宁愿选择fcuandy的代码

[No_Data_Found]

学习 收藏 接分

[viva369]

按存在相同数据的字段排序确实会有这个问题，但楼主的方法也行不通，如果表中数据量大的话，
同时按2个字段排序(不做任何操作，不做任何链接，但排序)，消耗服务器断的资源将很大，在页面中基本就是超时...
接着找解决方案............

[lhik2002]

爱家生活网www.wj880.com
问答互动平台www.housewj.cn
问答互动平台www.lift114.cn