千万级数据查询

港控/mmm° 2022-09-26 14:00 263阅读 0赞

\---千万级数据查询  
if (object\_id(N'syspr\_TablePaginationSearch',N'P') is not null)  
drop procedure dbo.syspr\_TablePaginationSearch  
go  
create procedure syspr\_TablePaginationSearch  
(  
@SelectField nvarchar(512)='\*'     -- Select语句字段,调用的时候不要输入"select"  
,@FormTables nvarchar(512)      -- Form子句，表名,包括架构名，调用的时候不要输入"from"  
,@WhereField nvarchar(512)=null     -- Where语句、判断字段，调用的时候不要输入"where"  
\-----------------------------------------------------  
,@OverPartitionField nvarchar(512)=null   -- 分区字段，  
,@OverOrderField nvarchar(512)     -- 分页字段，主要排序字段,必须  
,@GroupField nvarchar(512)=null     -- 分组字段  
,@HavingField nvarchar(512)=null    -- 分组判断条件  
,@OrderField nvarchar(512)=null     -- 排序字段  
\-----------------------------------------------------  
,@PageSize int=10        -- 页长  
,@Page int=1         -- 页  
\-----------------------------------------------------  
,@ErrorProcedure nvarchar(128)=null output -- 错误存储过程名  
,@ErrorNumber int=0 output      -- 错误编号  
,@ErrorMessage nvarchar(512)=null output -- 错误信息  
)  
as  
begin  
\--定义第一行记录  
declare @firstRow int;  
\--定义最后一条记录  
declare @lastRow int;--设置第一条记录  
set @firstRow=((@Page-1)\*@PageSize)+1;  
\--设置最后一条记录  
set @lastRow=@firstRow+@PageSize;--定义查询SQL字段  
declare @sqlString nvarchar(512);  
\--定义Select语句，内查询使用  
declare @selectString nvarchar(512);  
\--定义Where字段，内查询使用  
declare @whereString nvarchar(512);  
\--定义Form子句，内查询使用  
declare @formString nvarchar(512);  
\--定义分组字段，内查询使用  
declare @groupString nvarchar(512);  
\--定义Having子句，内查询使用  
declare @havingString nvarchar(512);  
\--定义排序字段，外查询使用  
declare @OrderString nvarchar(512);--组装Select子句-----------------------------------  
set @SelectField=isnull(@SelectField,N' \* ');  
if(@SelectField='')  
   set @SelectField=N' \*' ;  
\--组装select子句到row\_number()  
set @selectString=N' select '+ @SelectField+ ', Row\_Number() over( ';  
\--组装到分区partition by 表达式  
set @OverPartitionField=isnull(@OverPartitionField,N'');  
if (@OverPartitionField<>'')  
   set @selectString= @selectString+ ' partition by '+ @OverPartitionField;  
\--组装到排序分页order by 表达式  
set @OverOrderField=isnull(@OverOrderField,N'');  
if (@OverOrderField<>'')  
   set @selectString= @selectString+ ' order by '+ @OverOrderField+ N') as RowNumber ';  
\--over---------------------------------------------  
\--组装Form子句------------------------------------  
set @formString= N' from '+ @FormTables;  
\--组装Where判断子句------------------------------  
set @WhereField=isnull(@WhereField,N'');  
if(@WhereField<>'')  
   set @whereString=N' where '+ @WhereField;  
else  
   set @whereString=N'';  
\--where判断子句组装完毕-------------------------  
\--组装group by分组子句--------------------------  
set @GroupField=isnull(@GroupField,N'');  
if (@GroupField<>'')  
begin  
   set @groupString= N' group by '+ @GroupField;   --组装having分组判断条件  
   set @HavingField= isnull(@HavingField,N'');  
   if (@HavingField<> N'')  
    set @havingString= N' having '+ @HavingField;  
   else  
    set @havingString=N'';  
end  
else  
begin  
   set @groupString= N'';  
   set @havingString=N'';  
end  
\--over-------------------------------------------  
\--组装order by外排序子句-----------------------  
set @OrderField=isnull(@OrderField,N'');  
if (@OrderField<>'')  
   set @OrderString= N' order by '+ @OverOrderField+ N' , '+ @OrderField;  
else  
   set @OrderString= N'';  
\--如果@groupString不为空，则外排序不起作用  
if (@groupString<>N'')  
   set @OrderString=N'';  
\--over-------------------------------------------  
\--组装分页查询语句  
set @sqlString=  
   N'select \* '+  
   N'from '+  
   N' ( '+  
    @selectString+  
    @formString+  
    @whereString+  
    @groupString+  
    @havingString+  
   N' ) as TB '+  
   N'where '+  
   N' TB.RowNumber>='+cast(@firstRow as nvarchar(10))+  
   N' and TB.RowNumber<'+cast(@lastRow as nvarchar(10))+  
   @orderString  
\--over-------------------------------------------  
\--执行SQL，返回影响行数;如有异常，则抛出，并赋值输出参数  
begin try  
   exec (@sqlString);  
   return @@rowcount;  
end try  
begin catch  
   set @ErrorProcedure=Error\_Procedure();  
   set @ErrorNumber=error\_number();  
   set @ErrorMessage=error\_message();  
end catch;end  
go-----------------------------------------------------------  
\--简单的测试,查询AdventureWorks库的Person.Address表。  
\--以addressid字段顺序分页、并按PostalCode ,StateProvinceID等字段排序。exec dbo.syspr\_TablePaginationSearch  
@SelectField='\*'  
,@FormTables='AdventureWorks.Person.Address'  
,@whereField='addressid>100'  
,@OverOrderField='addressid asc'  
,@OrderField='PostalCode ,StateProvinceID'  
,@Page=1  
,@PageSize=10  
go  
\--简单的测试,分组统计addressid字段，并分页显示结果集。exec dbo.syspr\_TablePaginationSearch  
@SelectField='count(addressid) as addressCount,city'  
,@FormTables='AdventureWorks.Person.Address'  
,@OverOrderField='count(addressid) desc'  
,@GroupField='city'  
,@Page=1  
,@PageSize=10  
go  
\--连接查询并分页  
exec dbo.syspr\_TablePaginationSearch  
@SelectField=N'  
   addr.AddressLine1,  
   addr.AddressLine2,  
   addr.City,  
   sp.Name as ProvinceName,  
   addr.PostalCode,  
   addr.rowguid,  
   addr.ModifiedDate'  
,@FormTables=N'  
   AdventureWorks.Person.Address as addr  
   inner join  
   AdventureWorks.Person.StateProvince as sp  
    on addr.StateProvinceID=sp.StateProvinceID'  
,@OverOrderField='addressid asc'  
,@Page=1  
,@PageSize=10  
go