|
|
Posted on 2006-04-12 13:00 laifangsong 阅读(4519) 评论(44) 编辑 收藏 网摘 所属分类: sqlserver2000
注意：该存储过程处理逻辑被发现有严重错误，并不能实现 任意排序等功能。
  /**//*
 说明：1.支持多表查询 2.支持任意排序 3.不支持表别名
参考了
evafly920:[分享]千万数量级分页存储过程（效果演示）
地址：http://blog.csdn.net/evafly920/archive/2006/03/03/614813.aspx
IF(EXISTS(SELECT * FROM sysobjects WHERE [id]=OBJECT_ID('usp_PagingLarge') AND xtype='P'))
DROP PROCEDURE usp_PagingLarge
 */
GO
CREATE PROCEDURE usp_PagingLarge
@TableNames VARCHAR(200), --表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR(100), --主键，可以为空，但@Order为空时该值不能为空
@Fields VARCHAR(200), --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize INT, --每页记录数
@CurrentPage INT, --当前页，0表示第1页
@Filter VARCHAR(200) = '', --条件，可以为空，不用填 where
@Group VARCHAR(200) = '', --分组依据，可以为空，不用填 group by
@Order VARCHAR(200) = '' --排序，可以为空，为空默认按主键升序排列，不用填 order by
AS
BEGIN
DECLARE @SortColumn VARCHAR(200)
DECLARE @Operator CHAR(2)
DECLARE @SortTable VARCHAR(200)
DECLARE @SortName VARCHAR(200)
IF @Fields = ''
SET @Fields = '*'
IF @Filter = ''
SET @Filter = 'WHERE 1=1'
ELSE
SET @Filter = 'WHERE ' + @Filter
IF @Group <>''
SET @Group = 'GROUP BY ' + @Group
IF @Order <> ''
BEGIN
DECLARE @pos1 INT, @pos2 INT
SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
IF CHARINDEX(' DESC', @Order) > 0
IF CHARINDEX(' ASC', @Order) > 0
BEGIN
IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
SET @Operator = '<='
ELSE
SET @Operator = '>='
END
ELSE
SET @Operator = '<='
ELSE
SET @Operator = '>='
SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
SET @pos1 = CHARINDEX(',', @SortColumn)
IF @pos1 > 0
SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
SET @pos2 = CHARINDEX('.', @SortColumn)
IF @pos2 > 0
BEGIN
SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
IF @pos1 > 0
SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
ELSE
SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
END
ELSE
BEGIN
SET @SortTable = @TableNames
SET @SortName = @SortColumn
END
END
ELSE
BEGIN
SET @SortColumn = @PrimaryKey
SET @SortTable = @TableNames
SET @SortName = @SortColumn
SET @Order = @SortColumn
SET @Operator = '>='
END
DECLARE @type varchar(50)
DECLARE @prec int
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @TopRows INT
SET @TopRows = @PageSize * @CurrentPage + 1
print @TopRows
print @Operator
EXEC('
DECLARE @SortColumnBegin ' + @type + '
SET ROWCOUNT ' + @TopRows + '
SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
SET ROWCOUNT ' + @PageSize + '
SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '
')
END
GO
--调用例子：
--1.单表/单排序
EXEC usp_PagingLarge 'bigtable','d_id','d_id,d_title,d_content,d_time',20,1,'','','d_id desc'
--2.单表/多排序
EXEC usp_PagingLarge 'bigtable','d_id','*',20,0,'','','d_time asc,d_id desc'
--3.多表/单排序
EXEC usp_PagingLarge 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author', 20, 0, '', '', 'bigtable.d_id asc'
--4.多表/多排序
EXEC usp_PagingLarge 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author', 20, 0, '', '', 'bigtable.d_time asc,bigtable.d_id desc'与自定义分页结合例子： /Files/jiny-z/Paging_Custom.rar
Feedback
不会 Oracle，你可以网上多搜几次，应该有吧。
hertcloud.cnblogs.com
这里的更好吧
在网上现在还没有在功能上，性能都可以的Oracle的分页存储，找了好多，都是在性能太差了！大家谁有好点的Oracle的分页存储
Oracle的分页，语言本身有支持，不需要存储过程。
另外，SQL2005也已经提供了语言本身的支持。
不需要再这么费劲了。
@学习中
你给的地址粗粗看了一下效率，感觉是一样的，用的是很经典的2个top
select top @PageSize where 主键>=(select max()...)
还好没用2个top＋not in(...)，不然效率肯定要比 set rowcount ...低，呵呵。
我用的是 set rowcount ...
好像千万数据分页，访问的页数越大，用游标做分页效率越高。
我分页选择顺序是：
1。set rowcount
2。2个top (not in 对几百页以后的访问就很慢了，不过没人会点到几百页去)
3。asc/desc (最后一页还是错的，郁闷。。。)
4。临时表(不管点到哪一页，所用时间都一样，50W条的我试过，要10秒左右，不能用)
其实分页原理也就那么3、4种
我这个也是改其他网友的作品的，没什么技术可言。目的只是让它更适合自己用，更通用而已，效率差别都不大，呵呵。
这两句
41 SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
42 IF CHARINDEX(' DESC', @Order) > 0 AND CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
可能有点不够准确，如果我有个字段叫 description 并且排序 description asc 可能会有点问题，：）
@发芽的豆子
谢谢你，你说的没错，但你说的会出问题的代码 CHARINDEX(' DESC', @Order)中的
' DESC'中 DESC 前面是有空格的，你可能没看到，这样就保证了绝对不会和字段重复，因为字段中是不允许有空格的。所以不会发生你说的那个问题。
呵呵，一般这样调用都不会有问题：
description asc,description1 asc
description前面不要输入空格，逗号后面不要输入空格。
请博主把 代码 的 行号 去除吧，这样比较好粘贴呀。
刚才我发现了一个bug，已经修改了。
结合自定义分页，速度挺快的，我在本机上都是立刻出来，不会有等待，服务器上会慢些。
如果你order by 的字段不是主键的话，最好给这个字段加个索引，查询速度会快很多。
楼主得演示页才2w 多数据，即使用临时表方法也不会太慢亚！千万得数据得时候性能高并没有体现出来 亚
2w 多数据？没有啊，我本机是50w的数据啊。
这个存储过程效率应该还是可以的，呵呵。。
@laifangsong
' DESC'中 DESC 前面是有空格的，你可能没看到，这样就保证了绝对不会和字段重复，因为字段中是不允许有空格的。所以不会发生你说的那个问题。
应该不成立。刚好SQL Server支持字段名带空格，加上方括号或者引号即可。再者，前面带空格后面也没有带空格。
我倒觉得可以将预处理放到代码中，存贮过程中只执行准备好的代码，这样应该更安全。
另外，千万行的数据在什么时候需要分页？
千万行的数据在什么时候需要分页？ 双鱼座老大的这个问题经典.
@双鱼座
//SQL Server支持字段名带空格，加上方括号或者引号即可.
谢谢你，你说对，可以带。字段是否可以带空格一开始我并没测试过，只是主观地认为肯定不能带，刚才测了一下，感觉很惭愧。。。呵呵。
但其实只要字段不要命名成部分有 空格＋asc或者 空格＋desc，是完全可以避免这个问题的啊，会经常有人将字段命名成带空格的吗？应该很少吧。
千万的数据分页的时候多不多呢？你开发经验肯定很丰富，说的对，应该不多。但说出千万的字眼并不是哗众取宠，只是说明能负荷的数据量啊，呵呵。
//我倒觉得可以将预处理放到代码中，存贮过程中只执行准备好的代码，这样应该更安全。
安全，为什么呢？呵呵。
你使用charindex, 这会"引起全表扫描",
所以这个存储过程效率不会好到哪儿的.
@马志远
不会引起"引起全表扫描", 因为 charindex()函数内并没有字段，仅仅是判断一个字符串中某个子串的位置。
exec PPPPP @TableNames = 'Question', @PrimaryKey = '',
@Fields = '',
@PageSize = 10, @CurrentPage =1, @Filter = '[QuestionType_ID]=1 And [IsPublic] =1 And IsDel=0', @Group = '',
@Order = 'IsTop desc,Question_CreateTime desc'
这样执行分页不正确
我的第二页只有一条数据
显示的依然是十条
期待解决
@blueKnight
有你说的那个问题.
我事先考虑不全.
我会改一下，发一份给你。谢谢测试。
我有两张表,Select表A的结果需要根据表B中和表A有关联的数据个数来排序
是不是你这个存储过程不支持Count(B.ACode)这样的排序条件?
这个存储过程根本不能正常分页，因为在程序中还象缺少主键也可以分页，这是绝对不可能的，分页存储过程一定要有主键的参与，其它键有重复，请作者认识到这一点.
mrdream@126.com
分页一定要用不重复的键来作每一条记录的标记，如果不用主键就不能标识每一条记录，前面有一位同志反映的：
这样执行分页不正确
我的第二页只有一条数据
显示的依然是十条
期待解决
这是因为存槠过程没有使用主键，排序的键中有重复，当翻到第二页时，第10和11条都符合条件.
测试过了，还有问题呀
记录读取并不正确。有没有修改后的无错版本呀？？
请问可以给个Asp的调用方法吗，我如下调用上述储存过程
'CommandText = "PageList_0_Pagination '" + TableNames + "','" + PrimaryKey + "','" + Fields + "'," + CStr(PageSize) + "," + CStr(CurrentPage) + ",'" + Filter + "','" + Group + "','" + Order + "'"
'Set Class_Rs = Class_Conn.Execute(CommandText)
经管我已经在前面打开了Conn连接对象，可是，最后要输出GetRows的时候，总是提示我对象没有打开。
打印出 CommandText 看看在 查询分析器里执行有没问题？
这个存储过程还有问题，请看上面的回复。
现在没时间修改。抱歉。
谢谢楼主的回复，在查询分析器里执行非常正常，就是无法用Asp执行储存过程的方式来调用。
@星空[匿名]
建议先自己写一个最简单的不带参数的存储过程，然后用
Set Class_Rs = Class_Conn.Execute(CommandText)这种方法调用。
如果有错误，那说明你调用方法不对。这个可能性比较小。
如果没有错误，再试试给这个最简单的存储过程带个参数，调用一下。
加了参数没出现错误，一般能肯定是这个分页存储过程参数有问题。检查带入的参数。
加了参数出现错误，那么请看看ado参考，带参的调用要注意什么。
看到你发的存储过程，感觉很深哦
不怎么懂，向你请教下
我在用的时，在排序上，字段里，是重复的可以吗？
我在用当中出现问题。
详细能说一下吗？
在sql2005中执行查询为什么报错
21
<=
消息 156，级别 15，状态 1，第 3 行
关键字 'SET' 附近有语法错误。
消息 137，级别 15，状态 1，第 4 行
必须声明标量变量 "@SortColumnBegin"。
消息 137，级别 15，状态 2，第 6 行
必须声明标量变量 "@SortColumnBegin"。
牛人们 help me ！
创建一个带参数的存储过程 用于建表
表名由参数输入
LZ,使用条件查询不怎么灵呀，不如我想用 Like 老是报错如下：
测试语句：
EXEC usp_PagingLarge 'kc2','pk3','',20,0,'bh like '%z%'','','bh desc'
报错：
服务器: 消息 170，级别 15，状态 1，行 1
第 1 行: '%' 附近有语法错误。
请看看我那里写的不对啊。
我36条数据，前三页都是10条/页，到了第4页 出现了5条记录，然后最后一页也有一条记录（这条记录和第4页的最后一条重复）
这是怎么回事，望解答……
我的信箱:gdl1532@sina.com
多字段排序有问题。。。。，多个字段排序除主键外，其它字段排序如果有重复的就会有问题。。。
楼主好好测度一下吧，
|