文档章节

MSSQL分页存储过程

dong_zq
 dong_zq
发布于 2016/07/16 14:52
字数 1395
阅读 3
收藏 0

本文转自http://www.oschina.net/code/snippet_933757_33542借鉴学习

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
README
Version 1.0.5
本存储过程是本人查找对比网络上常用的分页存储过程后改写的分页存储过程,拥有较高效率,但不保证效率最高。
本存储过程是在MSSQL2012上编写,不保证兼容所有版本MSSQL(已知MSSQL2005需要修改少量代码)。不兼容其他数据库。
本分页存储过程仅支持常用SQL语句。
若发现问题,请联系ttio4116@live.com,共同进步!!
 
版本更新:
Version 1.0.1:修复了查询第一页数据时会采用错误的SQL语句的BUG
Version 1.0.2:更新了部分参数的解释
Version 1.0.3:修复了多表查询时由于表名错误导致的出错
Version 1.0.4:去除了部分不需要的条件判断语句
Version 1.0.5:修复了第一页时GROUP BY的错误,现在可以在@FldName里面加入COUNT()、MAX()等(不限于此)聚合函数
 
注意:1.HAVING语句需要与GROUP BY语句配合使用,格式为:GROUP BY XXX HAVING XXX。
      2.@FldSort需要给需要排序的字段设置ASC或者DESC。
      3.@strOrder为排序&聚合参数,排序时在没有设置@FldSort时起作用(默认正序排列);聚合时为了计算总条数,若该参数为空时则取@FldSort的第一个字段。
      4.建议@strOrder设置为主键,就算不是主键也不要包含NULL,否则会发生不可预料的结果。若该参数为空,请务必使@FldSort的第一个字段不含NULL。
*/
CREATE PROCEDURE [dbo].[MyPageRead]
(
@TblName nvarchar(3000)     --连接的表名,即FROM后面的内容
,@FldName nvarchar(3000)='*'     --要查询的字段名称,默认为全部
,@FldSort nvarchar(3000)=NULL     --排序字段,不需要ORDER BY,排序自行设置,请加入ASC或者DESC
,@strCondition nvarchar(3000)=NULL     --要查询的语句,不需要WHERE,前面不需要跟AND或者OR,但是不会影响计算
,@strGroup nvarchar(3000)=NULL     --要聚合的语句,不需要GROUP BY
,@strHaving nvarchar(3000)=NULL     --HAVING语句,不需要HAVING
,@Dist bit=0     --是否去除重复数据,0不去除/1去除
,@strOrder nvarchar(1000)=NULL     --一个排序字段,当@FldSort为空时必须指定。而且该字段用于计算总条数,该字段为空时选取@FldSort的第一个字段
,@OnlyCounts bit=0     --是否只返回总条数而不进行分页
 
,@PageSize int=10     --每页要显示的数量
,@Page int=1     --要显示那一页的数据
 
,@Counts int=1 output     --返回总条数
,@PageCounts int=1 output     --返回总页数
)
AS
SET NOCOUNT ON     --不返回计数
--定义变量
DECLARE @tmpFldSort nvarchar(3000)     --构成的ORDER BY语句存放处
DECLARE @tmpstrCondition nvarchar(3000)     --WHERE语句存放处
DECLARE @tmpstrGroup nvarchar(3000)     --GROUP BY语句存放处
DECLARE @tmpstrfirst nvarchar(3000)     --1.开头存放处,控制Dist
DECLARE @tmpstrfirstCount nvarchar(3000)     --2.开头存放处,控制Dist
 
/*计算时间*/
DECLARE @StartTime datetime
SET @StartTime=GETDATE()
 
 
IF (@FldSort IS NULL OR @FldSort='') AND (@strOrder IS NULL OR @strOrder='')
    RETURN
--必须有一个有值,如果有问题,直接跳出
 
IF @FldSort IS NULL OR @FldSort=''
    SET @tmpFldSort=@strOrder+' ASC '
ELSE
    SET @tmpFldSort=@FldSort
--以上为设置ORDER BY语句
 
IF @strCondition IS NULL OR @strCondition=''
    SET @tmpstrCondition=''
ELSE
    BEGIN
        IF CHARINDEX('AND ',LTRIM(@strCondition))=1
            SET @strCondition=RIGHT(@strCondition,LEN(@strCondition)-4)
        IF CHARINDEX('OR ',LTRIM(@strCondition))=1
            SET @strCondition=RIGHT(@strCondition,LEN(@strCondition)-3)
        SET @tmpstrCondition=' WHERE '+@strCondition
    END
--以上为设置WHERE语句
 
IF @strGroup IS NULL OR @strGroup=''
    SET @tmpstrGroup=''
ELSE
    BEGIN
        SET @tmpstrGroup=' GROUP BY '+@strGroup
        IF @strHaving IS NOT NULL AND @strHaving<>''
            SET @tmpstrGroup=@tmpstrGroup+' HAVING '+@strHaving
    END
--以上为设置GROUP BY语句
 
DECLARE @tmpFldsubstr nvarchar(1000)     --排序的第一个字段,用于计算总数据量,当@strOrder无数据时有效
IF @Dist=0
    BEGIN
        SET @tmpstrfirst=' SELECT '
        IF @strOrder IS NULL OR @strOrder=''
            BEGIN
                SET @tmpFldsubstr=LEFT(LTRIM(@FldSort),CHARINDEX(CHAR(32),LTRIM(@FldSort)))
                SET @tmpstrfirstCount=' SELECT @Counts=COUNT('+@tmpFldsubstr+')'
            END
        ELSE
            SET @tmpstrfirstCount=' SELECT @Counts=COUNT('+@strOrder+')'
    END
ELSE
    BEGIN
        SET @tmpstrfirst=' SELECT DISTINCT '
        IF @strOrder IS NULL OR @strOrder=''
            BEGIN
                SET @tmpFldsubstr=LEFT(LTRIM(@FldSort),CHARINDEX(CHAR(32),LTRIM(@FldSort)))
                SET @tmpstrfirstCount=' SELECT @Counts=COUNT(DISTINCT '+@tmpFldsubstr+')'
            END
        ELSE
            SET @tmpstrfirstCount=' SELECT @Counts=COUNT(DISTINCT '+@strOrder+')'
    END
--以上通过@Dist设置@Counts
 
DECLARE @sqlStr nvarchar(3000)     --查询的sql语句
IF @tmpstrGroup=''
    SET @sqlStr=@tmpstrfirstCount+' FROM '+@TblName+@tmpstrCondition
ELSE
    BEGIN
        SET @tmpstrfirstCount=REPLACE(@tmpstrfirstCount,'@Counts=','')
        SET @sqlStr='SELECT @Counts=COUNT(*) FROM ('+@tmpstrfirstCount+'AS tmpF FROM '+@TblName+@tmpstrCondition+@tmpstrGroup+') AS tmpT'
    END
EXEC sp_executesql @sqlStr,N'@Counts int out ',@Counts out     --返回查找到的总数
 
IF @OnlyCounts=1
    RETURN
--如果@OnlyCounts=1,则直接返回总条数
 
DECLARE @tmpCounts int
IF @Counts=0
    SET @tmpCounts=1 
ELSE
    SET @tmpCounts=@Counts
 
SET @PageCounts=(@tmpCounts+@PageSize-1)/@PageSize
--以上获得分页总数
 
IF @Page<1
    SET @Page=1
IF @Page>@PageCounts
    SET @Page=@PageCounts
--以上设置分页
 
DECLARE @tmpsql nvarchar(3000)     --设置最后要查询的SQL语句
 
IF @Page=1     --当取第一页时,用最快的算法
    SET @tmpsql=@tmpstrfirst+' TOP '+CAST(@PageSize AS nvarchar(50))+' '+@FldName+' FROM '+@TblName+@tmpstrCondition+@tmpstrGroup+' ORDER BY '+@tmpFldSort
IF @Page>1 AND @Page<=@PageCounts/2     --这是要查询的页在总分页数的前半
    BEGIN
        SET @tmpsql='WITH temptbl AS(SELECT TOP '+CAST(@Page*@PageSize AS nvarchar(50))+' ROW_NUMBER() OVER(ORDER BY '+@tmpFldSort+') AS tmpRowIndex,'+@FldName+' FROM '+@TblName+' '+@tmpstrCondition+' '+@tmpstrGroup+') '
        SET @tmpsql=@tmpsql+'SELECT * FROM temptbl WHERE [tmpRowIndex] BETWEEN '+CAST((@Page-1)*@PageSize+1 AS nvarchar(50))+' AND '+CAST((@Page-1)*@PageSize+@PageSize AS nvarchar(50))
    END
IF @Page>1 AND @Page>@PageCounts/2     --从后面查在总分页数的后半数据
    BEGIN
        SET @tmpFldSort=REPLACE(@tmpFldSort,' ASC',' [~1]')
        SET @tmpFldSort=REPLACE(@tmpFldSort,' DESC',' [~2]')
        SET @tmpFldSort=REPLACE(@tmpFldSort,'[~1]','DESC')
        SET @tmpFldSort=REPLACE(@tmpFldSort,'[~2]','ASC')
        --优化后半部分数据查询,把查询条件互换,[~1]为DESC,[~2]为ASC
        SET @tmpsql='WITH temptbl AS(SELECT TOP '+CAST(@Counts-(@Page-1)*@PageSize AS nvarchar(50))+' ROW_NUMBER() OVER(ORDER BY '+@tmpFldSort+') AS tmpRowIndex,'+@FldName+' FROM '+@TblName+' '+@tmpstrCondition+' '+@tmpstrGroup+') '
        SET @tmpsql=@tmpsql+'SELECT * FROM temptbl WHERE [tmpRowIndex] BETWEEN '+CAST(@Counts-((@Page-1)*@PageSize+@PageSize-1) AS nvarchar(50))+' AND '+CAST(@Counts-((@Page-1)*@PageSize) AS nvarchar(50))+' ORDER BY tmpRowIndex DESC'
    END
 
--SELECT @tmpsql     查看拼接的字符串
EXEC sp_executesql @tmpsql
 
/*计算时间*/
--SELECT DATEDIFF(MS,@StartTime,GETDATE()) AS [Time]
/**/
SET NOCOUNT OFF

 

本文转载自:http://www.oschina.net/code/snippet_933757_33542

共有 人打赏支持
dong_zq
粉丝 0
博文 10
码字总数 5386
作品 0
郑州
高级程序员
FreeBSD下执行SQL Server2005存储过程解决方案

FreeBSD下执行SQL Server2005存储过程解决方案(Warning: mssql_execute(): ...) FreeBSD下使用SQL Server2005(2008)需要使用FreeTDS。 安装FreeTSD后,使用PHP语言进行测试。在使用mssql_e...

雷xiao雨
2014/03/01
0
0
折腾Oracle问题小菜记[分页存储过程/查询所有表、视图、存储过程/查询表、视图、存储过程字段结构与参数](三)

说明: 为了让CYQ.Data 框架支持Oracle,这几天对Oracle进行了基本探索,并把中间遇到的问题轻轻的记录了下来,与大伙共享。 总共有三篇: 1:初折腾Oracle问题小记 2:初折腾Oracle问题小记二...

晨曦之光
2012/03/09
0
0
SQLSERVER 高效分页查询

Sqlserver数据库分页查询一直是Sqlserver的短板,闲来无事,想出几种方法,假设有表ARTICLE,字段ID、YEAR...(其他省略),数据53210条(客户真实数据,量不大),分页查询每页30条,查询第1500页...

木头
2013/01/30
0
0
jmeter(八)-JDBC请求(sqlserver)

做JDBC请求,首先要了解这个JDBC对象是什么,然后寻找响应的数据库连接URL和数据库驱动。 数据库URL:jdbc:sqlserver://200.99.197.190:1433;databaseName=ebank 数据库驱动:com.microsoft...

劲风online
2015/01/08
0
0
基于AEAI ESB的处理大字段

1.背景 数据迁移中大字段一直是个比较头疼的问题,一旦大字段中值大于32K的话就无法用sql语句来实现插入和更新。通用的解决办法一般有两种:存储过程和JDBC。下面主要总结下基于AEAI ESB用J...

数通畅联
2015/01/22
0
0

没有更多内容

加载失败,请刷新页面

加载更多

利用碎片化时间Get Linux系统

起初,我做着一份与IT毫无关系的工作,每月领着可怜的工资,一直想改变现状,但无从下手,也就是大家熟知的迷茫。我相信,每一个人都会或多或少的经历过迷茫,迷茫每一个选择,迷茫工作或者生...

Linux就该这么学
34分钟前
0
0
图像显示深入学习一:Activity启动过程

一个月左右写了图像显示深入学习之文章开篇文章表明了自己近期的计划,前半年重新学习了opengl es,c++以及Linux的一些知识,觉得是时候开始看图像这一块的源码了,边看边补缺补漏吧。 作为该...

JerryLin123
56分钟前
1
0
给MySQL授权远程访问

putty登录服务器; 登录MySQL: mysql -u root -p 新建远程用户: CREATE USER 'myusername' IDENTIFIED BY 'mypassword'; 授权: grant all on *.* to john@'101.102.103.104' identified by......

sweethome
今天
1
0
在t-io老巢造谣,不过有造谣的就会有反造谣的!

只发当事人的截图,不发表评论,以免有引导嫌疑 PS: 截图是由不同的人发过来的 本人已经不在此微信群 图3:有造谣的,就有反造谣的 图4是2018-09-23的t-io官方群的一个发言小统计,有助于让...

talent-tan
今天
102
0
heartbeat 资源

drbd+apache+heartbeat : http://blog.51cto.com/11838039/1827901 heartbeat双机热备的架设 : http://blog.51cto.com/11838039/1827560 对heaetbeat的深一步认识 : http://blog.51cto.co......

寰宇01
今天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部