有表别名

时间:2019-11-22 23:54来源:快三在线投注平台数据库
好久没有上来写点东西了,今天正好有空,共享一些个人心得,就是关于分页的存储过程,这个问题应该是老生重谈了,网上的通用存储过程的类型已经够多了,但是,好象看到的基本

好久没有上来写点东西了,今天正好有空,共享一些个人心得,就是关于分页的存储过程,这个问题应该是老生重谈了,网上的通用存储过程的类型已经够多了,但是,好象看到的基本上不能够满足一些复杂的SQL语句的分页,比如下面这句: select''asCheckBox,A.TargetID,A.TargetPeriod,Convert,B.BeginDate,120)asBeginDate, Convert,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 leftouterjoinChlItemCatalogasDonA.ItemCatalog=D.CatalogCode leftouterjoinChlOrgasEonA.OrgID=E.OrgID leftouterjoinChlOrgasFonC.BranchOrgID=F.OrgID whereA.TargetPeriod>='200607'andA.TargetPeriod<='200608'andF.OrgCodelike'%123%'andE.OrgCodelike'%123%' orderbyA.TargetPerioddesc,C.SalesName,D.CatalogName上面这句SQL里面有一些特殊情况,比如使用了Convert函数,而且没有主键,有多表连接,有表别名,字段别名等等,这些情况处理起来可能比较棘手,当然,其中的“''asCheckBox”是我系统当中的特例情况,用来做一些处理的。 我这里提供一个自己开发的通用分页存储过程,有什么好的建议和意见,大家请不吝指教。代码如下:

通用分页存储过程----Sp_Paging /**//*

功能:通用分页存储过程 参数: @PKvarchar,主键,用来排序的单一字段,空的话,表示没有主键,存储过程将自动创建标识列主键 @Fieldsvarchar,要显示的字段列表 @Tablesvarchar @Wherevarchar,查询条件 @OrderByvarchar,排序条件(支持多个排序字段,如:ID,Codedesc,Namedesc) @PageIndexint,当前要显示的页的页索引,索引从1开始,无记录时为0。 @PageSizeint,页大小 创建者:HollisYao 创建日期:2006-08-06 备注: ============================================================ */ CreatePROCEDURE[dbo].[Sp_Paging] @PKvarchar='', @Fieldsvarchar, @Tablesvarchar, @Wherevarchar='', @OrderByvarchar, @PageIndexint, @PageSizeint AS --替换单引号,避免构造SQL出错 set@Fields=replace --要执行的SQL,切分为几个字符串,避免出现长度超过4k时的问题 declare@SQL1varchar declare@SQL2varchar set@SQL1='' set@SQL2='' if@Whereisnotnullandlen>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'--声明一个变量,页索引 set@SQL1=@SQL1+'declare@StartRowint'--声明一个变量,当前页第一条记录的索引 set@SQL1=@SQL1+'select@TotalCount=countfrom'+@Tables+@Where--获取总记录数 set@SQL1=@SQL1+'if@PageCount<=0begin'--如果记录数为0,直接输出空的结果集 set@SQL1=@SQL1+'select'+@Fields+'from'+@Tables+'where1<>1' set@SQL1=@SQL1+'select0asPageIndex,0asPageCount,'+convert+'asPageSize,0asTotalCount' set@SQL1=@SQL1+'returnend' set@SQL1=@SQL1+'set@PageCount=(@TotalCount+'+convert+'-1)/'+convert--获取总页数 set@SQL1=@SQL1+'set@PageIndex='+convert--设置正确的页索引 set@SQL1=@SQL1+'if@PageIndex<0set@PageIndex=1' set@SQL1=@SQL1+'if@PageIndex>@PageCountand@PageCount>0set@PageIndex=@PageCount' set@SQL1=@SQL1+'set@StartRow=*'+convert+'+1' if(charindex=0andcharindex begin --**************************************************************************** --****************不需要创建主键******************************************** --**************************************************************************** declare@SortDirectionvarchar--排序方向,>=:升序,<=:倒序 set@SortDirection='>=' ifcharindex>0 set@SortDirection='<=' set@SQL2=@SQL2+'declare@Sortvarchar'--声明一个变量,用来记录当前页第一条记录的排序字段值 set@SQL2=@SQL2+'setrowcount@StartRow'--设置返回记录数截止到当前页的第一条 set@SQL2=@SQL2+'select@Sort='+@PK+'from'+@Tables+@Where+'orderby'+@OrderBy--获取当前页第一个排序字段值 set@SQL2=@SQL2+'setrowcount'+convert--设置返回记录数为页大小 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 set@SQL2=@SQL2+'setrowcount@EndRow' set@SQL2=@SQL2+'declare@PKBeginint'--声明一个变量,开始索引 set@SQL2=@SQL2+'declare@PKEndint'--声明一个变量,结束索引 set@SQL2=@SQL2+'set@PKBegin=@StartRow' set@SQL2=@SQL2+'set@PKEnd=@EndRow' --**************************************************************************** --************对特殊字段进行转换,以便可以插入到临时表****************** --**************************************************************************** declare@TempFieldsvarchar set@TempFields=@Fields set@TempFields=replace(@TempFields,'''''asCheckBox','') set@TempFields=replace(@TempFields,'''''asDetailButton','') set@TempFields=replace(@TempFields,'''''asRadio','') set@TempFields=LTRIM ifleft=','--去除最左边的逗号 set@TempFields=substring(@TempFields,2,len ifright=','--去除最右边的逗号 set@TempFields=substring(@TempFields,1,len set@SQL2=@SQL2+'selectidentityasPK,'+@TempFields+'into#tbfrom'+@Tables+@Where+'orderby'+@OrderBy --**************************************************************************** --********去除字段的表名前缀,当有字段有别名时,只保留字段别名********* --**************************************************************************** declare@TotalFieldsvarchar declare@tmpvarchar 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 begin set@i=charindex --去除字段的表名前缀 if begin --找不到逗号分割,即表示只剩下最后一个字段 set@tmp=@Fields end else begin set@tmp=substring end set@j=charindex set@tmp=substring --*******当有字段有别名时,只保留字段别名********* --带括号的情况要单独处理,如Convert,B.EndDate,120)asEndDate while>0) begin set@iLeft=@iLeft+1 set@tmp=substring(@tmp,charindex) end while>0) begin set@iRight=@iRight+1 set@tmp=substring',@tmp)+1,Len end --当括号恰好组队的时候,才能进行字段别名的处理 if begin set@iLeft=0 set@iRight=0 --不对这几个特殊字段作处理:CheckBox、DetailButton、Radio if(charindex=0andcharindex=0andcharindex begin --判断是否有别名 if>0)--别名的第一种写法,带'as'的格式 begin set@tmp=substring(@tmp,charindex+2,len end else begin if--别名的第二种写法,带空格的格式 begin while begin set@tmp=substring(@tmp,charindex end end end end set@TotalFields=@TotalFields+@tmp end if set@Fields='' else set@Fields=substring(@Fields,@i+1,len end --print@TotalFields set@SQL2=@SQL2+'select'+@TotalFields+'from#tbwherePKbetween@PKBeginand@PKEndorderbyPK'--输出最终显示结果 set@SQL2=@SQL2+'droptable#tb' end --输出“PageIndex、PageSize” set@SQL2=@SQL2+'select@PageIndexasPageIndex,@PageCountasPageCount,' +convert+'asPageSize,@TotalCountasTotalCount' --print@SQL1+@SQL2 exec 如果使用这个通用分页存储过程的话,那么调用方法如下:

使用通用分页存储过程进行分页 /**//*

功能:获取销售目标,根据条件 参数: @UserTypeint, @OrgIDvarchar, @TargetPeriodBeginnvarchar, @TargetPeriodEndnvarchar, @BranchOrgCodenvarchar, @BranchOrgNamenvarchar, @OrgCodenvarchar, @OrgNamenvarchar, @SalesCodenvarchar, @SalesNamenvarchar, @CatalogCodenvarchar, @CatalogNamenvarchar, @PageIndexint,当前要显示的页的页索引,索引从1开始,无记录时为0。 @PageSizeint,页大小 创建者:HollisYao 创建日期:2006-08-11 备注: ============================================================ */ CreatePROCEDURE[dbo].[GetSalesTargetList] @UserTypeint, @OrgIDnvarchar, @TargetPeriodBeginnvarchar, @TargetPeriodEndnvarchar, @BranchOrgCodenvarchar, @BranchOrgNamenvarchar, @OrgCodenvarchar, @OrgNamenvarchar, @SalesCodenvarchar, @SalesNamenvarchar, @CatalogCodenvarchar, @CatalogNamenvarchar, @PageIndexint, @PageSizeint AS declare@Conditionnvarchar set@Condition='' if set@Condition=@Condition+'andA.OrgIDin' if(len>0) set@Condition=@Condition+'andA.TargetPeriod>='''+@TargetPeriodBegin+'''' if>0) set@Condition=@Condition+'andA.TargetPeriod<='''+@TargetPeriodEnd+'''' if>0) set@Condition=@Condition+'andF.OrgCodelike''%'+@BranchOrgCode+'%''' if>0) set@Condition=@Condition+'andF.OrgNamelike''%'+@BranchOrgName+'%''' if set@Condition=@Condition+'andE.OrgCodelike''%'+@OrgCode+'%''' if set@Condition=@Condition+'andE.OrgNamelike''%'+@OrgName+'%''' if set@Condition=@Condition+'andC.SalesCodelike''%'+@SalesCode+'%''' if set@Condition=@Condition+'andC.SalesNamelike''%'+@SalesName+'%''' if set@Condition=@Condition+'andD.CatalogCodelike''%'+@CatalogCode+'%''' if set@Condition=@Condition+'andD.CatalogNamelike''%'+@CatalogName+'%''' if set@Condition=substring(@Condition,5,len --print@Condition execsp_Paging N'',N'''asCheckBox,A.TargetID,A.TargetPeriod,Convert,B.BeginDate,120)asBeginDate,Convert,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

编辑:快三在线投注平台数据库 本文来源:有表别名

关键词: