本文作者：草上飞
网址：
www.newbooks.com.cn
QQ：1469886
如需转载，请保留以上信息。
谢谢！
点击下载支持多表关联任意字段排序的存储过程分页SQL>>>
网上有很多现成的存储过程分页sql，但是大多数只支持主键字段或者唯一值字段进行排序。而对于有重复值的字段进行排序的时候，数据会遗漏。
而且很多存储过程分页脚本也不支持多表关联查询的分页，而我们现实应用中，一般都是多表关联的查询，针对这几个问题，我网上随便找了一个
现成的脚本，进行了一下修改。修改后的脚本支持多表查询和任意字段排序（包括数值型字段）。
以下脚本注释以我开发的新书城网上书店（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）的分页时候整理出来的。本存储过程可以任意转载，但在转载过程中请保留以上信息。谢谢！
新书城网上书店（www.newbooks.com.cn）现有30几万的图书数量，利用该存储过程达到了根据价格、销量、出版日期等字段快速排序的效果，具体分页速度可进入该网站进行查看。
如对该存储过程有疑问，请与本人联系。
 */
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
|
|
转播
