高效分页方法代码(sql百万级数据量分页代码)

/*–用存款和储蓄进程达成的分页程序 展现钦命表、视图、查询结果的第X页
对于表中主键或标志列的动静,直接从原表取数查询,此外景况选拔有时表的不二诀窍假如视图或询问结果中有主键,不引入此格局 –邹建二〇〇四.09–*/ /*–调用示例
execp_show’地区资料’
execp_show’地区资料’,5,3,’地区编号,地区称号,助记码’,’地区编号’ –*/
/*
因为要顾及通用性,所以对带排序的查询语句有自然供给.假诺先排序,再出结果.便是:
execp_show’selecttop100percent*from地区资料orderby地区名称’,5,3,’地区编号,地区称号,助记码’,’地区称号’
–查询语句加上:top100percent//top时 */
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_show]’)andOBJECTPROPERTY(id,N’IsProcedure’)=1)
dropprocedure[dbo].[p_澳门金沙vip,show] GO CreateProcp_show
@QueryStrnvarchar(4000卡塔尔国,–表名、视图名、查询语句
@PageSizeint=10,–每页的大大小小(行数卡塔尔 @PageCurrentint=1,–要显得的页
@FdShownvarchar(4000卡塔尔国=”,–要显得的字段列表,若是查询结果有标志字段,必要内定此值,且不含有标志字段
@FdOrdernvarchar(1000卡塔尔=”–排序字段列表 as
declare@FdNamenvarchar(250卡塔尔(قطر‎–表中的主键或表、有时表中的标记列名
,@Id1varchar(20卡塔尔国,@Id2varchar(20State of Qatar–开端和终止的笔录号
,@Obj_IDint–对象ID –表中有复合主键的处理declare@strfdnvarchar(二〇〇二卡塔尔国–复合主键列表
,@strjoinnvarchar(4000State of Qatar–连接字段 ,@strwherenvarchar(二零零二卡塔尔国–查询条件
select@Obj_ID=object_id(@QueryStr)
,@FdShow=caseisnull(@FdShow,”)when”then’*’else”+@FdShowend
,@FdOrder=caseisnull(@FdOrder,”)when”then”else’orderby’+@FdOrderend
,@QueryStr=casewhen@Obj_IDisnotnullthen”+@QueryStrelse'(‘+@QueryStr+’卡塔尔a’end
–假若显示第一页,能够直接用top来达成 if@PageCurrent=1 begin
select@Id1=cast(@PageSizeasvarchar(20卡塔尔国卡塔尔国exec(‘selecttop’+@Id1+@FdShow+’from’+@QueryStr+@FdOrder卡塔尔 return end
–要是是表,则检查表中是还是不是有标记更或主键
if@Obj_IDisnotnullandobjectproperty(@Obj_ID,’IsTable’)=1 begin
select@Id1=cast(@PageSizeasvarchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSizeasvarchar(20))
select@FdName=namefromsyscolumnswhereid=@Obj_IDandstatus=0x80
if@@rowcount=0–如若表中无标记列,则检查表中是还是不是有主键 begin
ifnotexists(select1fromsysobjectswhereparent_obj=@Obj_IDandxtype=’PK’卡塔尔gotolbusetemp–假如表中无主键,则用有的时候表处理select@FdName=namefromsyscolumnswhereid=@Obj_IDandcolidin(
selectcolidfromsysindexkeyswhere@Obj_ID=idandindidin(
selectindidfromsysindexeswhere@Obj_ID=idandnamein(
selectnamefromsysobjectswherextype=’PK’andparent_obj=@Obj_ID 卡塔尔卡塔尔国卡塔尔if@@rowcount1–检查表中的主键是或不是为复合主键 begin
select@strfd=”,@strjoin=”,@strwhere=”
select@strfd=@strfd+’,[‘+name+’]’
,@strjoin=@strjoin+’anda.[‘+name+’]=b.[‘+name+’]’
,@strwhere=@strwhere+’andb.[‘+name+’]isnull’
fromsyscolumnswhereid=@Obj_IDandcolidin(
selectcolidfromsysindexkeyswhere@Obj_ID=idandindidin(
selectindidfromsysindexeswhere@Obj_ID=idandnamein(
selectnamefromsysobjectswherextype=’PK’andparent_obj=@Obj_ID )))
select@strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000) gotolbusepk end end end else
gotolbusetemp /*–使用标志列或主键为单一字段的管理办法–*/
lbuseidentity: exec(‘selecttop’+@Id1+@FdShow+’from’+@QueryStr
+’where’+@FdName+’notin(selecttop’
+@Id2+”+@FdName+’from’+@QueryStr+@FdOrder +’)’+@FdOrder ) return
/*–表中有复合主键的拍卖方法–*/ lbusepk:
exec(‘select’+@FdShow+’from(selecttop’+@Id1+’a.*from
(selecttop100percent*from’+@QueryStr+@FdOrder+’)a
leftjoin(selecttop’+@Id2+”+@strfd+’
from’+@QueryStr+@FdOrder+’)bon’+@strjoin+’ where’+@strwhere+’)a’ )
return /*–用一时表管理的主意–*/ lbusetemp:
select@FdName='[ID_’+cast(newid()asvarchar(40))+’]’
,@Id1=cast(@PageSize*(@PageCurrent-1)asvarchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1asvarchar(20))
exec(‘select’+@FdName+’=identity(int,0,1),’+@FdShow+’
into#tbfrom’+@QueryStr+@FdOrder+’
select’+@FdShow+’from#tbwhere’+@FdName+’between’ +@Id1+’and’+@Id2 ) GO

久远未有上来写点东西了,前天刚刚有空,分享一些私有心得,便是关于分页的积累过程,那些难点应有是老生重谈了,网络的通用存款和储蓄进程的连串已经够多了,可是,好象见到的大半不可以看到满意一些复杂的SQL语句的分页,比方下边那句:
select”asCheckBox,A.TargetID,A.TargetPeriod,Convert(varchar(10State of Qatar,B.BeginDate,120卡塔尔asBeginDate,
Convert(varchar(10卡塔尔(قطر‎,B.EndDate,120卡塔尔(قطر‎asEndDate,C.SalesCode,C.SalesName,D.CatalogCode,D.CatalogName,
E.OrgID,E.OrgName,F.OrgIDasBranchOrgID,F.OrgCodeasBranchOrgCode,F.OrgNameasBranchOrgName,
A.Amount,”asDetailButton fromChlSalesTargetasA
leftouterjoinChlSalesTargetPeriodasBonA.TargetPeriod=B.TargetPeriod
leftouterjoinChlSalesasConA.Sales=C.SalesCode
leftouterjoinChlItemCatalo瓦斯DonA.ItemCatalog=D.CatalogCode
leftouterjoinChlOr瓦斯EonA.OrgID=E.OrgID
leftouterjoinChlOr瓦斯FonC.BranchOrgID=F.OrgID
whereA.TargetPeriod=’二〇〇六07’andA.TargetPeriod=’二〇〇七08’andF.OrgCodelike’%123%’andE.OrgCodelike’%123%’
orderbyA.TargetPerioddesc,C.SalesName,D.CatalogName上边那句SQL里面有局地非正规意况,举例动用了Convert函数,并且尚未主键,有多表连接,有表外号,字段小名等等,那几个情形管理起来只怕相比较吃力,当然,在那之中的“”asCheckBox”是自小编系统个中的特例境况,用来做一些拍卖的。
小编那边提供贰个团结开支的通用分页存款和储蓄进度,有哪些好的提商谈思想,我们请多多点拨。代码如下:

火速分页方法代码(sql百万级数据量分页代码卡塔尔

通用分页存款和储蓄进度—-Sp_Paging /**//*

成效:通用分页存款和储蓄进程 参数:
@PKvarchar(50卡塔尔,主键,用来排序的单一字段,空的话,表示不曾主键,存款和储蓄进程将活动创造标记列主键
@Fieldsvarchar(500卡塔尔(قطر‎,要出示的字段列表(格式如:ID,Code,Name卡塔尔国@Tablesvarchar(1000卡塔尔国,要选用的表集结(Org)@Wherevarchar(500卡塔尔,查询条件(Codelike’100’卡塔尔@OrderByvarchar(100卡塔尔,排序条件(协理多少个排序字段,如:ID,Codedesc,Namedesc)@PageIndexint,当前要突显的页的页索引,索引从1始发,无记录时为0。
@PageSizeint,页大小 创制者:HollisYao 创造日期:2005-08-06 备注:
============================================================ */
CreatePROCEDURE[dbo].[Sp_Paging] @PKvarchar(50卡塔尔=”,
@菲尔德svarchar(500卡塔尔, @Tablesvarchar(1000State of Qatar, @Wherevarchar(500卡塔尔国=”,
@OrderByvarchar(100State of Qatar, @PageIndexint, @PageSizeint AS
–替换单引号,防止构造SQL出错 set@Fields=replace(@Fields,””,”””卡塔尔(قطر‎–要实践的SQL,切分为多少个字符串,防止现身长度超越4k时的题目declare@SQL1varchar(4000State of Qatar declare@SQL2varchar(4000State of Qatar set@SQL1=”
set@SQL2=” if@Whereisnotnullandlen(ltrim(rtrim(@WhereState of Qatar卡塔尔国卡塔尔0
set@Where=’where’+@Where else set@Where=’where1=1′
set@SQL1=@SQL1+’declare@TotalCountint’–声宾博个变量,总记录数
set@SQL1=@SQL1+’declare@PageCountint’–声飞鹤个变量,总页数
set@SQL1=@SQL1+’declare@PageIndexint’–注解一(Wissu卡塔尔国个变量,页索引
set@SQL1=@SQL1+’declare@StartRowint’–声圣元(Synutra卡塔尔个变量,当前页第一条记下的索引
set@SQL1=@SQL1+’select@TotalCount=count(*卡塔尔(قطر‎from’+@Tables+@Where–获取总记录数
set@SQL1=@SQL1+’if@PageCount=0begin’–假使记录数为0,间接输出空的结果集
set@SQL1=@SQL1+’select’+@菲尔德s+’from’+@Tables+’where11′
set@SQL1=@SQL1+’select0asPageIndex,0asPageCount,’+convert(varchar,@PageSize卡塔尔国+’asPageSize,0asTotalCount’
set@SQL1=@SQL1+’returnend’
set@SQL1=@SQL1+’set@PageCount=(@TotalCount+’+convert(varchar,@PageSize卡塔尔国+’-1State of Qatar/’+convert(varchar,@PageSize卡塔尔国–获取总页数
set@SQL1=@SQL1+’set@PageIndex=’+convert(varchar,@PageIndexState of Qatar–设置科学的页索引
set@SQL1=@SQL1+’if@PageIndex0set@PageIndex=1′
set@SQL1=@SQL1+’if@PageIndex@PageCountand@PageCount0set@PageIndex=@PageCount’
set@SQL1=@SQL1+’set@StartRow=(@PageIndex-1卡塔尔(قطر‎*’+convert(varchar,@PageSize)+’+1′
if(charindex(‘,’,@OrderBy)=0andcharindex(@PK,@OrderBy)0) begin
–****************************************************************************
–****************无需创建主键********************************************
–****************************************************************************
declare@SortDirectionvarchar(10卡塔尔–排序方向,=:升序,=:倒序
set@SortDirection=’=’ ifcharindex(‘desc’,@OrderBy卡塔尔0
set@SortDirection=’=’
set@SQL2=@SQL2+’declare@Sortvarchar(100卡塔尔’–声圣元(Synutra卡塔尔(قطر‎个变量,用来记录当前页第一条记下的排序字段值
set@SQL2=@SQL2+’setrowcount@StartRow’–设置重返记录数截至到当前页的率先条
set@SQL2=@SQL2+’select@Sort=’+@PK+’from’+@Tables+@Where+’orderby’+@OrderBy–获取当前页第叁个排序字段值
set@SQL2=@SQL2+’setrowcount’+convert(varchar,@PageSize卡塔尔(قطر‎–设置再次来到记录数为页大小
set@Where=@Where+’and’+@PK+@SortDirection+’@Sort’
set@SQL2=@SQL2+’select’+@Fields+’from’+@Tables+@Where+’orderby’+@OrderBy–输出最终呈现结果
end else begin
–****************************************************************************
–*************亟需创制自拉长主键******************************************
–****************************************************************************
set@SQL2=@SQL2+’declare@EndRowint’
set@SQL2=@SQL2+’set@EndRow=@PageIndex*’+convert(varchar,@PageSize)set@SQL2=@SQL2+’setrowcount@EndRow’
set@SQL2=@SQL2+’declare@PKBeginint’–声美赞臣(Meadjohnson卡塔尔(قطر‎个变量,开首索引
set@SQL2=@SQL2+’declare@PKEndint’–声雅培(Abbott卡塔尔国个变量,甘休索引
set@SQL2=@SQL2+’set@PKBegin=@StartRow’
set@SQL2=@SQL2+’set@PKEnd=@EndRow’
–****************************************************************************
–************对新鲜字段进行更动,以便能够插入到一时表******************
–****************************************************************************
declare@TempFieldsvarchar(500卡塔尔国 set@TempFields=@Fields
set@TempFields=replace(@TempFields,””’asCheckBox’,”)set@TempFields=replace(@TempFields,””’asDetailButton’,”卡塔尔set@TempFields=replace(@TempFields,””’asRadio’,”卡塔尔国set@TempFields=LTENVISIONIM(RTHavalIM(@Temp菲尔德s卡塔尔(قطر‎)ifleft(@Temp菲尔德s,1State of Qatar=’,’–去除最左侧包车型客车逗号
set@TempFields=substring(@TempFields,2,len(@TempFields卡塔尔国)ifright(@Temp菲尔德s,1卡塔尔国=’,’–去除最侧面的逗号
set@Temp菲尔德s=substring(@TempFields,1,len(@TempFieldsState of Qatar-1)set@SQL2=@SQL2+’selectidentity(int,1,1State of QatarasPK,’+@Temp菲尔德s+’into#tbfrom’+@Tables+@Where+’orderby’+@OrderBy
–****************************************************************************
–********除去字段的表名前缀,当有字段有别名时,只保留字段别称*********
–****************************************************************************
declare@TotalFieldsvarchar(500卡塔尔 declare@tmpvarchar(50卡塔尔 declare@iint
declare@jint declare@iLeftint–左括号的个数
declare@iRightint–右括号的个数 set@i=0 set@j=0 set@iLeft=0 set@iRight=0
set@tmp=” set@TotalFields=” while(len(@Fields卡塔尔国0State of Qatar begin
set@i=charindex(‘,’,@Fields卡塔尔国 –去除字段的表名前缀 if(@i=0State of Qatar begin
–找不到逗号分割,即意味着只剩余最终一个字段 set@tmp=@Fields end else
begin set@tmp=substring(@Fields,1,@i卡塔尔(قطر‎ end set@j=charindex(‘.’,@tmpState of Qatarif(@j0卡塔尔 set@tmp=substring(@tmp,@j+1,len(@tmp卡塔尔国卡塔尔国–*******当有字段有小名时,只保留字段外号*********
–带括号的情形要独自管理,如Convert(varchar(10卡塔尔,B.EndDate,120卡塔尔国asEndDate
while(charindex(‘(‘,@tmpState of Qatar0卡塔尔(قطر‎ begin set@iLeft=@iLeft+1
set@tmp=substring(@tmp,charindex(‘(‘,@tmp卡塔尔(قطر‎+1,Len(@tmp卡塔尔国State of Qatar end
while(charindex(‘卡塔尔(قطر‎’,@tmpState of Qatar0卡塔尔国 begin set@iRight=@iRight+1
set@tmp=substring(@tmp,charindex(‘卡塔尔’,@tmp卡塔尔(قطر‎+1,Len(@tmp卡塔尔(قطر‎卡塔尔(قطر‎ end
–当括号正好组成代表队的时候,能力开展字段小名的管理 if(@iLeft=@iRight卡塔尔(قطر‎ begin
set@iLeft=0 set@iRight=0
–不对那多少个例外字段作管理:CheckBox、DetailButton、Radio
if(charindex(‘CheckBox’,@tmpState of Qatar=0andcharindex(‘DetailButton’,@tmp卡塔尔(قطر‎=0andcharindex(‘Radio’,@tmp卡塔尔=0卡塔尔begin –推断是不是有别称if(charindex(‘as’,@tmp卡塔尔0卡塔尔国–小名的第一种写法,带’as’的格式 begin
set@tmp=substring(@tmp,charindex(‘as’,@tmp卡塔尔(قطر‎+2,len(@tmpState of Qatar卡塔尔国 end else begin
if(charindex(”,@tmp卡塔尔(قطر‎0卡塔尔–外号的第两种写法,带空格(“”卡塔尔(قطر‎的格式 begin
while(charindex(”,@tmp卡塔尔(قطر‎0卡塔尔国 begin
set@tmp=substring(@tmp,charindex(”,@tmp卡塔尔(قطر‎+1,len(@tmp卡塔尔卡塔尔 end end end end
set@TotalFields=@TotalFields+@tmp end if(@i=0卡塔尔国 set@Fields=” else
set@Fields=substring(@Fields,@i+1,len(@Fields卡塔尔国卡塔尔(قطر‎ end –print@TotalFields
set@SQL2=@SQL2+’select’+@TotalFields+’from#tbwherePKbetween@PKBeginand@PKEndorderbyPK’–输出最后彰显结果
set@SQL2=@SQL2+’droptable#tb’ end
–输出“PageIndex(页索引卡塔尔国、PageCount(页数卡塔尔国、PageSize(页大小卡塔尔(قطر‎、TotalCount(总记录数卡塔尔(قطر‎”
set@SQL2=@SQL2+’select@PageIndexasPageIndex,@PageCountasPageCount,’
+convert(varchar,@PageSize卡塔尔国+’asPageSize,@TotalCountasTotalCount’
–print@SQL1+@SQL2 exec(@SQL1+@SQL2State of Qatar假如接受那些通用分页存款和储蓄进度的话,那么调用方法如下:

@querystr nvarchar(300卡塔尔国,–表名、视图名、查询语句@pagesize
int=10,–每页的尺寸(行数State of Qatar@pagecurrent int=1,–要来得的页@fdshow nvarchar
(100卡塔尔国=”,–要展现的字段列表,如若查询结果有标记字段,需求指

动用通用分页存款和储蓄进程举办分页 /**//*

意义:获收取售目的,依据条件 参数: @UserTypeint, @OrgIDvarchar(500卡塔尔(قطر‎,
@TargetPeriodBeginnvarchar(50卡塔尔国, @TargetPeriodEndnvarchar(50State of Qatar,
@BranchOrgCodenvarchar(50卡塔尔国, @BranchOrgNamenvarchar(50卡塔尔(قطر‎,
@OrgCodenvarchar(50State of Qatar, @OrgNamenvarchar(50卡塔尔, @SalesCodenvarchar(50卡塔尔国,
@SalesNamenvarchar(50卡塔尔, @CatalogCodenvarchar(50卡塔尔(قطر‎,
@CatalogNamenvarchar(50卡塔尔(قطر‎,
@PageIndexint,当前要出示的页的页索引,索引从1方始,无记录时为0。
@PageSizeint,页大小 成立者:HollisYao 创设日期:贰零零陆-08-11 备注:
============================================================ */
CreatePROCEDURE[dbo].[GetSalesTargetList] @UserTypeint,
@OrgIDnvarchar(500), @TargetPeriodBeginnvarchar(50),
@TargetPeriodEndnvarchar(50), @BranchOrgCodenvarchar(50),
@BranchOrgNamenvarchar(50), @OrgCodenvarchar(50), @OrgNamenvarchar(50),
@SalesCodenvarchar(50), @SalesNamenvarchar(50),
@CatalogCodenvarchar(50), @CatalogNamenvarchar(50), @PageIndexint,
@PageSizeint AS declare@Conditionnvarchar(2000) set@Condition=”
if(@UserType1) set@Condition=@Condition+’andA.OrgIDin(‘+@OrgID+’)’
if(len(@TargetPeriodBegin)0)
set@Condition=@Condition+’andA.TargetPeriod=”’+@TargetPeriodBegin+””
if(len(@TargetPeriodEnd)0)
set@Condition=@Condition+’andA.TargetPeriod=”’+@TargetPeriodEnd+””
if(len(@BranchOrgCode)0)
set@Condition=@Condition+’andF.OrgCodelike”%’+@BranchOrgCode+’%”’
if(len(@BranchOrgName)0)
set@Condition=@Condition+’andF.OrgNamelike”%’+@BranchOrgName+’%”’
if(len(@OrgCode)0)
set@Condition=@Condition+’andE.OrgCodelike”%’+@OrgCode+’%”’
if(len(@OrgName)0)
set@Condition=@Condition+’andE.OrgNamelike”%’+@OrgName+’%”’
if(len(@SalesCode)0)
set@Condition=@Condition+’andC.SalesCodelike”%’+@SalesCode+’%”’
if(len(@SalesName)0)
set@Condition=@Condition+’andC.SalesNamelike”%’+@SalesName+’%”’
if(len(@CatalogCode)0)
set@Condition=@Condition+’andD.CatalogCodelike”%’+@CatalogCode+’%”’
if(len(@CatalogName)0)
set@Condition=@Condition+’andD.CatalogNamelike”%’+@CatalogName+’%”’
if(len(@Condition)0)
set@Condition=substring(@Condition,5,len(@Condition)) –print@Condition
execsp_Paging
N”,N”’asCheckBox,A.TargetID,A.TargetPeriod,Convert(varchar(10),B.BeginDate,120)asBeginDate,Convert(varchar(10),B.EndDate,120)asEndDate,
C.SalesCode,C.SalesName,D.CatalogCode,D.CatalogName,E.OrgID,E.OrgName,F.OrgIDasBranchOrgID,F.OrgCodeasBranchOrgCode,F.OrgNameasBranchOrgName,A.Amount,”asDetailButton’,
N’ChlSalesTargetasA
leftouterjoinChlSalesTargetPeriodasBonA.TargetPeriod=B.TargetPeriod
leftouterjoinChlSalesasConA.Sales=C.SalesCode
leftouterjoinChlItemCatalogasDonA.ItemCatalog=D.CatalogCode
leftouterjoinChlOrgasEonA.OrgID=E.OrgID
leftouterjoinChlOrgasFonC.BranchOrgID=F.OrgID’, @Condition,
N’A.TargetPerioddesc,C.SalesName,D.CatalogName’, @PageIndex,@PageSize

定此值,且不包涵标志字段@fdorder nvarchar
(100卡塔尔(قطر‎=”,–排序字段列表@wherestr nvarchar (200卡塔尔国=”, –内容是’ id=3 and
model_no like ‘%24%’

and ‘@rscount int=0 output asset @fdshow=’ ‘+@fdshow+’ ‘set @fdorder= ‘
‘+@fdorder+’ ‘set @wherestr= ‘ ‘+@wherestr+’ ‘