`
chyx72
  • 浏览: 35149 次
  • 性别: Icon_minigender_1
  • 来自: 成都
社区版块
存档分类
最新评论

[转]SQL分页存储过程(not in模式、二分法实现)以及产生的问题

阅读更多
转:http://www.itphome.cn/shujukuyingyong/mssql/2010-01-27/149.html
二分法分页模式是常规分页模式的改进,但是如果排序字段有重复值时(即不是primarykey)就很有可能出现第一页和第二页内容相同,解决办法是1。用传统的分页,即去掉二分法功能,在数据量小的情况下没有效率差异。2.尽量用primarykey作为排序字段,如果不可以实现特殊功能的话可以用sql2005特有的功能,即隐藏字段(创建表或视图的时候会分配一隐含的id,即使对表进行插入删除操作,但是该字段永远是从小到大排列且唯一)

先看一下常规的分页存储过程

--非2分页 By JHF 2008*06*05
CREATE PROCEDURE [dbo].[proc_ListPage]

@tblName     nvarchar(200),        ----要显示的表或多个表的连接
@fldName     nvarchar(500) = '*',    ----要显示的字段列表
@pageSize    int = 10,        ----每页显示的记录个数
@page        int = 1,        ----要显示那一页的记录
@pageCount    int = 1 output,            ----查询结果分页后的总页数
@Counts    int = 1 output,                ----查询到的记录数
@fldSort    nvarchar(200) = null,    ----排序字段列表或条件
@Sort        bit = 1,        ----排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如:' SortA Asc,SortB Desc,SortC ')
@strCondition    nvarchar(1000) = null,    ----查询条件,不需where
@ID        nvarchar(150),        ----主表的主键
@Dist                 bit = 0           ----是否添加查询字段的 DISTINCT 默认0不添加/1添加

AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000)        ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000)        ----存放取得查询结果总数的查询语句
Declare @strID     nvarchar(1000)        ----存放取得查询开头或结尾ID的查询语句

Declare @strSortType nvarchar(10)    ----数据排序规则A
Declare @strFSortType nvarchar(10)    ----数据排序规则B

Declare @SqlSelect nvarchar(50)         ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50)          ----对含有DISTINCT的总数查询进行SQL构造


if @Dist = 0
begin
    set @SqlSelect = 'select '
    set @SqlCounts = 'Count(*)'
end
else
begin
    set @SqlSelect = 'select distinct '
    set @SqlCounts = 'Count(DISTINCT '+@ID+')'
end


if @Sort=0
begin
    set @strFSortType=' ASC '
    set @strSortType=' DESC '
end
else
begin
    set @strFSortType=' DESC '
    set @strSortType=' ASC '
end

 

--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null or @strCondition=''     --没有设置显示条件
begin
    set @sqlTmp = @fldName + ' From ' + @tblName
    set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
    set @strID = ' From ' + @tblName
end
else
begin
    set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition
    set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition
    set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition
end

----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
declare @tmpCounts int
if @Counts = 0
    set @tmpCounts = 1
else
    set @tmpCounts = @Counts

    --取得分页总数
    set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize

    /**//**当前页大于总页数 取最后一页**/
    if @page>@pageCount
        set @page=@pageCount

    --/*-----数据分页2分处理-------*/
    declare @pageIndex int --总数/页大小
    declare @lastcount int --总数%页大小

    set @pageIndex = @tmpCounts/@pageSize
    set @lastcount = @tmpCounts%@pageSize
    if @lastcount > 0
        set @pageIndex = @pageIndex + 1
    else
        set @lastcount = @pagesize

    --//***显示分页
    if @strCondition is null or @strCondition=''     --没有设置显示条件
    begin

                set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                        +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
                        +' order by '+ @fldSort +' '+ @strFSortType+')'
                        +' order by '+ @fldSort +' '+ @strFSortType 
         
    end

    else --有查询条件
    begin
       
                set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName +' from '+@tblName
                    +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
                    +' Where (1>0) ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType+')'
                    +' ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType                 
       
    end

------返回查询结果-----
exec sp_executesql @strTmp
--print @strTmp
SET NOCOUNT OFF

在比较一下二分法存储过程,当数据量很大时,原则上二分法省一半时间。

--created by JHF 2008*06*05

CREATE PROCEDURE [dbo].[proc_ListPage]

@tblName     nvarchar(200),        ----要显示的表或多个表的连接
@fldName     nvarchar(500) = '*',    ----要显示的字段列表
@pageSize    int = 10,        ----每页显示的记录个数
@page        int = 1,        ----要显示那一页的记录
@pageCount    int = 1 output,            ----查询结果分页后的总页数
@Counts    int = 1 output,                ----查询到的记录数
@fldSort    nvarchar(200) = null,    ----排序字段列表或条件
@Sort        bit = 1,        ----排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如:' SortA Asc,SortB Desc,SortC ')
@strCondition    nvarchar(1000) = null,    ----查询条件,不需where
@ID        nvarchar(150),        ----主表的主键
@Dist       bit = 0           ----是否添加查询字段的 DISTINCT 默认0不添加/1添加

AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000)        ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000)        ----存放取得查询结果总数的查询语句
Declare @strID     nvarchar(1000)        ----存放取得查询开头或结尾ID的查询语句

Declare @strSortType nvarchar(10)    ----数据排序规则A
Declare @strFSortType nvarchar(10)    ----数据排序规则B

Declare @SqlSelect nvarchar(50)         ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50)          ----对含有DISTINCT的总数查询进行SQL构造


if @Dist = 0
begin
    set @SqlSelect = 'select '
    set @SqlCounts = 'Count(*)'
end
else
begin
    set @SqlSelect = 'select distinct '
    set @SqlCounts = 'Count(DISTINCT '+@ID+')'
end


if @Sort=0
begin
    set @strFSortType=' ASC '
    set @strSortType=' DESC '
end
else
begin
    set @strFSortType=' DESC '
    set @strSortType=' ASC '
end

 

--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null or @strCondition=''     --没有设置显示条件
begin
    set @sqlTmp = @fldName + ' From ' + @tblName
    set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
    set @strID = ' From ' + @tblName
end
else
begin
    set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition
    set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition
    set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition
end

----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
declare @tmpCounts int
if @Counts = 0
    set @tmpCounts = 1
else
    set @tmpCounts = @Counts

    --取得分页总数
    set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize

    /**//**当前页大于总页数 取最后一页**/
    if @page>@pageCount
        set @page=@pageCount

    --/*-----数据分页2分处理-------*/
    declare @pageIndex int --总数/页大小
    declare @lastcount int --总数%页大小

    set @pageIndex = @tmpCounts/@pageSize
    set @lastcount = @tmpCounts%@pageSize
    if @lastcount > 0
        set @pageIndex = @pageIndex + 1
    else
        set @lastcount = @pagesize

    --//***显示分页
    if @strCondition is null or @strCondition=''     --没有设置显示条件
    begin
        if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理
            begin 
                set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                        +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
                        +' order by '+ @fldSort +' '+ @strFSortType+')'
                        +' order by '+ @fldSort +' '+ @strFSortType 
            end
        else
            begin
            set @page = @pageIndex-@page+1 --后半部分数据处理
                if @page <= 1 --最后一页数据显示
                    set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                        +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 
                else                
                    set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                        +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
                        +' order by '+ @fldSort +' '+ @strSortType+')'

                        +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 
            end
    end

    else --有查询条件
    begin
        if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理
        begin 
                set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName +' from '+@tblName
                    +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
                    +' Where (1>0) ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType+')'
                    +' ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType                 
        end
        else
        begin 
            set @page = @pageIndex-@page+1 --后半部分数据处理
            if @page <= 1 --最后一页数据显示
                    set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                        +' where (1>0) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
            else
                    set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                        +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
                        +' where (1>0) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+')'
                        + @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 
        end    
    end

------返回查询结果-----
exec sp_executesql @strTmp
--print @strTmp
SET NOCOUNT OFF
--由于项目使用的GUID为主键字段,这里使用了not in这样的查询

--调用方法列子:C#中

SqlParameter[] paras = new SqlParameter[8];
        paras[0] = new SqlParameter("@tblname", "news");
        paras[1] = new SqlParameter("@pagesize", PageSize);
        paras[2] = new SqlParameter("@page", CurPage);
        paras[3] = new SqlParameter("@fldsort", "convert(datetime,rtrim(repubdate))");
        paras[4] = new SqlParameter("@strCondition", strCondition);
        paras[5] = new SqlParameter("@id", "id");
        paras[6] = new SqlParameter("@PageCount", 0);
        paras[7] = new SqlParameter("@Counts", 0);

        SqlCommand cmd = db.createCmd(conn, "proc_listpage", paras);
        cmd.Parameters["@PageCount"].Direction = ParameterDirection.Output;   //设置页数变量为输出类型
        cmd.Parameters["@Counts"].Direction = ParameterDirection.Output;//设置总记录数为输出类型

        ds = db.getDataSet(cmd);
        DataList1.DataKeyField = "compid";
        DataList1.DataSource = ds.Tables[0];
        DataList1.DataBind();
        conn.Close();

         int pgcnt=Convert.ToInt32(cmd.Parameters["@PageCount"].Value);//获取两个输出类型变量
        int totalcnt=Convert.ToInt32(cmd.Parameters["@Counts"].Value);

--调用方法列子:asp中

<%
   '系统数据配置
   dim adCmdSPStoredProc
   dim adParamReturnValue
   dim adParaminput
  
   dim adParamOutput
   dim adBigInt
   dim adBinary
   dim adBoolean
   dim adChar
  
   dim adDBTimeStamp
   dim adEmpty
   dim adInteger
   dim adSmallInt
   dim adTinyInt
   dim adVarChar
  
   adCmdSPStoredProc = 4
   adParamReturnValue = 4
   adParaminput = 1
   adParamOutput = 2
   adBigInt=20
  
   adBinary=128 
   adBoolean=11 
   adChar=129 
   adDBTimeStamp=135 
   adEmpty=0 
  
   adInteger=3 
   adSmallInt=2 
   adTinyInt=16 
   adVarChar=200 
  
   dim compid,typeid,curpage
   dim tblName_varchar,fldName_varchar,pagesize_int,page_int,pagecount_int
   dim counts_int,fldSort_varchar,sort_bit,strCondition_varchar,ID_varchar,Dist_bit
  
   curpage=request("Page")
   if curpage="" then
    curpage="1"
   end if

///传参
   set cmd = Server.CreateObject("ADODB.Command")
   set rs=server.CreateObject("adodb.recordset")
   cmd.ActiveConnection = conn
   cmd.CommandText = "proc_ListPage"
   cmd.CommandType = adCmdSPStoredProc
   cmd.prepared=true
  
   cmd.parameters.append cmd.createparameter("@tblName",adVarChar,adParaminput,200,tblName_varchar)
   cmd.parameters.append cmd.createparameter("@tblName",adVarChar,adParaminput,200,fldName_varchar)
   cmd.parameters.append cmd.createparameter("@pageSize",adInteger,adParaminput,4,pagesize_int)
   cmd.parameters.append cmd.createparameter("@page",adInteger,adParaminput,4,page_int)
   cmd.parameters.append cmd.createparameter("@pageCount",adInteger,adParamoutput,4,pagecount_int)
   cmd.parameters.append cmd.createparameter("@Counts",adInteger,adParamoutput,4,counts_int)
   cmd.parameters.append cmd.createparameter("@fldSort",adVarChar,adParaminput,200,fldSort_varchar)
   cmd.parameters.append cmd.createparameter("@sort",adBoolean,adParaminput,1,sort_bit)
   cmd.parameters.append cmd.createparameter("@strCondition",adVarChar,adParaminput,1000,strCondition_varchar)
   cmd.parameters.append cmd.createparameter("@ID",adVarChar,adParaminput,150,ID_varchar)
   cmd.parameters.append cmd.createparameter("@Dist",adBoolean,adParaminput,1,Dist_bit)
  
   set rs=Server.CreateObject("ADODB.recordset")
   %>

设置参数值是非常必要的。

第三个是2005下包含隐含编号的存储过程。

CREATE PROCEDURE [dbo].[Pro_ListPage]
@Tables varchar(1000), --表名,多红表是请使用 tA a inner join tB b On a.AID = b.AID
@PK varchar(100),    --主键,可以带表头 a.AID
@Sort varchar(200) = '', --排序字段
@PageNumber int = 1,    --开始页码
@PageSize int = 10,        --页大小
@Fields varchar(1000) = '*',--读取字段
@Filter varchar(1000) = NULL,--Where条件
@Group varchar(1000) = NULL,  --分组
@isCount bit = 0     --1     --是否获得总记录数
AS
--
--select * from GL_NEWS order by GN_UPDATE_DATE DESC
--exec Pg_Paging @Tables = 'tb_NewsInfo', @PK = 'News_ID', @Sort = 'News_ID DESC', @PageNumber = 2, @PageSize = 15,@Fields = '*', @Group = '', @isCount = 0
DECLARE @strFilter varchar(2000)
declare @sql varchar(8000)
IF @Filter IS NOT NULL AND @Filter != ''
  BEGIN
    SET @strFilter = ' WHERE ' + @Filter + ' '
  END
ELSE
  BEGIN
    SET @strFilter = ''
  END

if @isCount = 1 --只获得记录条数
    begin
         set @sql = 'SELECT   Count(*) FROM ' + @Tables + @strFilter  
    end
else
begin
if @Sort = ''
   set @Sort = @PK + ' DESC '

IF @PageNumber < 1
   SET @PageNumber = 1

if @PageNumber = 1 --第一页提高性能
begin 
  set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ '   from ' + @Tables + ' ' + @strFilter + ' ORDER BY  '+ @Sort
end 
else
  begin
   DECLARE @START_ID varchar(50)
DECLARE @END_ID varchar(50)
SET @START_ID = convert(varchar(50),(@PageNumber - 1) * @PageSize + 1)
SET @END_ID = convert(varchar(50),@PageNumber * @PageSize)
    set @sql =  ' SELECT '+@Fields+ '
    FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum, 
     '+@Fields+ '
       FROM '+@Tables+') AS D
    WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort
  END

END
--print @sql

EXEC(@sql)

其用法相同,其调用可以封装为一个函数。
分享到:
评论

相关推荐

    SQL SERVER 分页查询二分法

    通常多表联查并且数据大时,分页查询时,会出现查询性能问题,查分页后面的数据,时间越久。但我们可以通过判断查询数据的总数据来进行相应的查询方式,从而保证性能。

    存储过程分页以及实现.docx

    总的来说,存储过程的分页实现涉及数据库设计、SQL查询优化和编程技巧。对于大型数据集,有效的分页策略可以显著提升用户体验,同时减少服务器资源的消耗。这个例子展示了如何结合存储过程和C#代码来实现这一目标,...

    用二分法实现棋盘覆盖

    下面将详细介绍二分法以及棋盘覆盖问题,以及如何在VC6.0中实现。 **二分法**,又称折半搜索或二分查找,是一种高效的搜索算法,适用于有序数据集。其基本思想是每次将待搜索区间缩小一半,直到找到目标值或区间为...

    二分法排序算法 C语言实现

    根据给定的文件信息,我们可以总结出以下关于“二分法排序算法...在C语言中实现二分查找,需要注意正确处理数组的创建、初始化以及查找过程中的边界条件。本例通过具体的代码实现了这一算法,可供学习者参考和实践。

    Aaron通用存储过程分页

    为了提高查询效率,本文将详细介绍一个名为`Pr_PageList_final`的通用存储过程,它采用了二分法加表变量整合的方式来实现千万级数据的高效分页查询。该存储过程支持自定义排序规则和过滤条件,并能够返回总记录数和...

    二分法的Java实现

    二分法的Java实现

    二分法(代码实现)

    下面是一个简单的二分法的Python代码实现: ```python def binary_search(arr, target): left, right = 0, len(arr) - 1 while left mid = (left + right) // 2 if arr[mid] == target: return mid elif arr...

    多线程+二分法的巧用——通达OA SQL盲注1

    【多线程+二分法在SQL盲注中的应用——以通达OA为例】 SQL盲注(SQL Blind Injection)是一种常见的网络安全漏洞,攻击者通过发送特定的SQL查询,利用服务器的响应时间或状态来推断数据库的信息。在这个案例中,...

    二分法算法实现 示例

    ### 二分法算法实现详解 #### 知识点一:二分法基本概念与原理 二分法,又称折半查找法,是一种在有序数组中查找特定元素的高效算法。其基本思想是通过将搜索区间不断折半,从而快速定位目标值的位置。具体步骤...

    用mfc实现二分法计算零点

    本教程将探讨如何使用MFC来实现二分法,这是一种常见的数值方法,常用于寻找函数的零点,即找到使函数值为零的自变量值。 二分法的基本思想是:对于一个连续函数f(x),如果在某个闭区间[a, b]内存在零点,即f(a) * ...

    python二分法实现实例

    1.算法:(设查找的数组期间为array[low, high]) (1)确定该期间的中间位置K(2)将查找的值T与array[k]比较。若相等,查找成功返回此位置;否则确定新的查找区域,继续二分查找。区域确定如下:a.array[k]&gt;T 由...

    VC++6.0 实现计算方法中的二分法

    在VC++6.0中,可以使用标准模板库(STL)中的容器,如vector,来存储数据,并用迭代器来实现二分法。同时,为了提高代码可读性和重用性,可以将二分法封装为一个函数,接受排序后的容器和目标值作为参数,返回目标的...

    用matlab实现二分法

    一个很简单的代码 f()为想要二分的函数

    SQL二分法双TOP

    SQL二分法。 双TOP二分 当前记录排序

    用C实现的二分法

    在这个C语言实现的例子中,二分法被用来找到函数`F(x) = x^4 - 3x + 1`的近似根。 首先,我们定义了`F(x)`,这是一个四次多项式,其系数分别为1、0、-3和1,常数项为1。函数的零点即为使得`F(x) = 0`的x值。`P`定义...

    matlab实现二分法,迭代法_matlab_二分法_迭代法_

    在实际应用中,二分法和迭代法常被用于求解各种工程问题,例如求解电路中的未知电压或电流、优化问题中的极值点,以及物理、化学等领域的各种模型问题。理解并熟练掌握这些数值方法对提升MATLAB编程技能和解决实际...

    温度转换二分法_二分法_温度二分法查表_40_

    标题中的“温度转换二分法”是指在处理温度测量时,采用二分法来查找温度对应的电压或电阻值。二分法,又称折半查找法,是一种高效的算法,常用于有序数据集合中寻找特定元素。在温度测量领域,如果我们有一个预设的...

    LabVIEW 二分法 简单实现

    简单的做了一个二分法的算法,小于某个特定值后停止运行。

    二分法实现查找数组中的最大次大值

    题目: 编程实现找出一组数的最大值和次大值 要求: 用二分法的策略实现; (2)写出实验报告。 一、 需求分析: 1、输入要输入数组元素的个数,为数组分配存储空间(动态数组); 2、输入数组元素; 3、利用二分法...

Global site tag (gtag.js) - Google Analytics