存储过程多表分页
来源：互联网 发布：nginx配置不同的tomcat 编辑：程序博客网 时间：2024/06/18 08:37
一.sql分页:先分页后多表连接查询
declare @row int;
select @row=COUNT(*) from BS_Subject where 1=1;
SELECT TOP 10 @row as Record, a.*,c.ClassName FROM BS_Subject a left join BS_Class c on a.ClassID=c.ClassID WHERE 1=1 and a.ID
not IN(SELECT TOP 1000000 ID FROM BS_Subject
where 1=1 ORDER BY ID desc)ORDER BY a.ID desc虽然这样可以快速查询出来，但是在程序里不好调用。
二.存储过程分页：单表，多表，多字段排序，总数
create PROCEDURE [dbo].[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
@RecordCount int –返回总页数 1只返回总数，0只返回当前页数据，2返回总数并返回当前页数据
AS
BEGIN
DECLARE @SortColumn VARCHAR(200)
DECLARE @Operator CHAR(2)
DECLARE @SortTable VARCHAR(200)
DECLARE @SortName VARCHAR(200)
declare @strSQL varchar(500)
if @RecordCount=1begin IF @Filter = '' begin set @strSQL = 'select count(*) as Total from ' + @TableNames end else begin set @strSQL = 'select count(*) as Total from ' + @TableNames + ' where '+@Filter end exec (@strSQL);endelsebegin 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 if @RecordCount=2 begin IF @Filter = '' begin set @strSQL = 'declare @PageRecordNumber int;select @PageRecordNumber=count(*) from ' + @TableNames+';' end else begin set @strSQL = 'declare @PageRecordNumber int;select @PageRecordNumber=count(*) from ' + @TableNames+' '+@Filter+';' end EXEC(@strSQL+' DECLARE @SortColumnBegin ' + @type + ' SET ROWCOUNT ' + @TopRows + ' SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + ' SET ROWCOUNT ' + @PageSize + ' SELECT ' + @Fields + ',@PageRecordNumber as Page_RecordNumber FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + ' ') end else begin 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 + ' ') endend end
调用
declare @ct datetime
set @ct = getdate()EXEC usp_PagingLarge ‘BS_Acode left join BS_Class on BS_Acode.ClassID=BS_Class.ClassID’,
‘BS_Acode.ID’,’BS_Acode.*,BS_Class.ClassName’,10,100,”,”,
‘BS_Acode.ID desc,BS_Acode.CreateDate desc’,2select datediff(ms, @ct ,getdate()) as ‘查询时间（毫秒）’
搜索了好多分页的存储过程，这个是挺好用的。可以支持单表、多表的查询，还可以排序（多字段排序），也可以返回总数，效率也好。之前用的一个，在前面的页数的时候查询很快，当到中后的页面的时候非常慢。
证明设置索引的必要性，我之前并相信设置这玩意儿会带来什么，关键是自己真不知道怎么看。其实是自己愚笨，不懂用了之后就说他没用。另外，非常确定ordery by真的能不用就用，经过尝试加上非常影响效率。
好友的总结，我收下了：
1.表的主键、外键必须有索引
2.数据量超过300的表应该有索引
3.经常与其他表进行连接的表，在连接字段上应该建立索引
4.经常出现在where字句中的字段，特别是大表的字段，应该建立索引
5.索引应该建在选择性高的字段上
6.索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建立索引
7.复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替
分页注意点：分页的时候一定要确保每一条数据都尤其自己的位置（顺序），比如按照热度排序的时候，就得order by 热度，发表时间，主键
这样就可以避免了在相同热度，相同发表时间里，再按照主键排序，就能将每个数据确定他的位置了。
————-好多东西都是需要慢慢完善的,再自己没有发现的时候,它都是最好的——————-
—上面的存储过程分页在分组查询的时候,不能order by分组字段。修改了一下，现在可以了
ALTER PROCEDURE [dbo].[usp_PagingLarge]
@TableNames VARCHAR(500), –表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR(500), –主键，可以为空，但@Order为空时该值不能为空
@Fields VARCHAR(500), –要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize INT, –每页记录数
@CurrentPage INT, –当前页，0表示第1页
@Filter VARCHAR(500) = ”, –条件，可以为空，不用填 where
@Group VARCHAR(500) = ”, –分组依据，可以为空，不用填 group by
@Order VARCHAR(500) = ”, –排序，可以为空，为空默认按主键升序排列，不用填 order by
@RecordCount int –返回总页数 1只返回总数，0只返回当前页数据，2返回总数并返回当前页数据
AS
BEGIN
DECLARE @SortColumn VARCHAR(200)
DECLARE @Operator CHAR(2)
DECLARE @SortTable VARCHAR(200)
DECLARE @SortName VARCHAR(200)
declare @strSQL varchar(500)
if @RecordCount=1begin IF @Filter = '' begin set @strSQL = 'select count(*) as Total from ' + @TableNames end else begin set @strSQL = 'select count(*) as Total from ' + @TableNames + ' where '+@Filter end exec (@strSQL);endelsebegin IF @Fields = '' SET @Fields = '*' IF @Filter = '' begin IF @Group <>'' begin SET @Filter = 'having 1=1' end else begin SET @Filter = 'WHERE 1=1' end end ELSE begin IF @Group <>'' begin SET @Filter = 'having '+@Filter end else begin SET @Filter = 'WHERE ' + @Filter end end 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 begin SET @type = @type + '(' + CAST(@prec AS varchar) + ')' end if @type is null and @Group<>'' begin set @type='int' end DECLARE @TopRows INT SET @TopRows = @PageSize * @CurrentPage + 1 if @RecordCount=2 begin IF @Filter = '' begin set @strSQL = 'declare @PageRecordNumber int;select @PageRecordNumber=count(*) from ' + @TableNames+';' end else begin set @strSQL = 'declare @PageRecordNumber int;select @PageRecordNumber=count(*) from ' + @TableNames+' '+replace(@Filter,'having','where')+';' end IF @Group <>'' begin --把分组和不分组的查询分开，查询条件和位置不一样 EXEC(@strSQL+' DECLARE @SortColumnBegin ' + @type + ' SET ROWCOUNT ' + @TopRows + ' SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Group+' '+@Filter + ' ORDER BY ' + @Order + ' SET ROWCOUNT ' + @PageSize + ' SELECT ' + @Fields + ',@PageRecordNumber as Page_RecordNumber FROM ' + @TableNames + ' ' + @Group+' '+@Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + ' ORDER BY ' + @Order + ' ') end else begin EXEC(@strSQL+' DECLARE @SortColumnBegin ' + @type + ' SET ROWCOUNT ' + @TopRows + ' SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + ' SET ROWCOUNT ' + @PageSize + ' SELECT ' + @Fields + ',@PageRecordNumber as Page_RecordNumber FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + ' ') end end else begin IF @Group <>'' begin --把分组和不分组的查询分开，查询条件和位置不一样 EXEC(' DECLARE @SortColumnBegin ' + @type + ' SET ROWCOUNT ' + @TopRows + ' SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Group +' '+@Filter+ ' ORDER BY ' + @Order + ' SET ROWCOUNT ' + @PageSize + ' SELECT ' + @Fields + ' FROM ' + @TableNames +' '+ @Group+' '+ @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + ' ORDER BY ' + @Order + ' ') end else begin 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 endend end调用
EXEC usp_PagingLarge
@TableNames = N’User_ExchangeGoods left join User_ExchangeOrder on User_ExchangeGoods.GID=User_ExchangeOrder.GoodsID’,
@PrimaryKey = N’User_ExchangeGoods.GID’,
@Fields = N’User_ExchangeGoods.GID,User_ExchangeGoods.GoodsName,User_ExchangeGoods.GoodsPrice,User_ExchangeGoods.ExchangeIntegral,cast(User_ExchangeGoods.GoodsPictures as varchar(max)) as GoodsPictures’,
@PageSize = 10,
@CurrentPage = 0,
@Filter = ‘1=1’,
@Group = N’User_ExchangeGoods.GID,User_ExchangeGoods.GoodsName,User_ExchangeGoods.GoodsPrice,User_ExchangeGoods.ExchangeIntegral,cast(User_ExchangeGoods.GoodsPictures as varchar(max))’,
@Order = ‘COUNT(User_ExchangeOrder.GoodsID) desc’,
@RecordCount = 0
上面，GoodPictures类型是text，会报错“不能比较或排序 text、ntext 和 image 数据类型，除非使用 IS NULL 或 LIKE 运算符。”，转换一下即可
- 存储过程多表分页
- 多表查询的存储过程分页
- 分页存储过程支持多表联合
- 多表查询分页存储过程
- 多表查询分页存储过程
- MYSQL多表分页存储过程
- 多条件分页存储过程
- 联合表 分页存储过程
- 分页存储过程，可支持多表查询[转]
- 高级自定义查询、分页、多表联合存储过程
- 支持多表分页查询的存储过程
- [MSSQL]SQL通用存储过程分页,支持多表联合
- 完整SQL分页存储过程（支持多表联接）
- 多表联合查询sql存储过程带分页
- SQL2005分页存储过程（支持多表联接）
- 数据库分页存储过程，支持多表联合查询
- 完整SQL分页存储过程（支持多表联接）
- 【MySql-多表联合查询分页存储过程】
- 留着要看的文章
- 迷宫问题
- 理解threadLocal
- 【quick-cocos2d-x】动画和粒子
- 上海Python培训学校哪个不错？
- 存储过程多表分页
- C 不可以直接对指针进行赋值！！！
- makefile自动化变量
- Linux driver dts使用，实例驱动编写
- P_004.Eclipse+pydev解决中文显示和注释问题的方法大全
- 百度编辑器ueditor的简单使用
- ZooKeeper典型应用场景
- 微博爬虫“免登录”技巧详解及 Java 实现
- docker容器链接外网的实现步骤
