一对多关系，按一聚合多(存储过程)

分页、查询、排序

作者：[e-mail]

create procedure sum_one--主表名@table_name varchar(50),--从表名@sec_table_name varchar(50),--主表主键名@col_name varchar(50),--从表外键名@sec_col_name varchar(50),--主表聚合列@pri_sum_col varchar(350),--从表聚合列@sec_sum_col varchar(350),--页大小,默认10@page_size int=10,--当前页,默认第一页@current_page int=1,--where子句,默认空,格式为 and+条件@the_where varchar(350)='',--排序列：1:主键名；2：“ck”主表聚合列；3：“rk”从表聚合列;4：“(ck-rk)”差值(默认)@order_TYPE varchar(50)='(ck-rk)',--升降(asc/desc),默认DESC@the_order varchar(10)='DESC'ASdeclare @the_sql varchar(5000)/*-----------set @table_name ='tbChuKuWuZiJiLu'set @sec_table_name ='tbKuCunJiLuBiao'set @col_name='char_CKWZJL_SN'set @sec_col_name ='vchar_XiangGuanRuKuWuZiSN'set @pri_sum_col ='float_CKWZJL_YingTiShuLiang'set @sec_sum_col ='float_KCJLB_ZhongLiang'set @the_where ='and 1=1'set @order_TYPE ='(ck-rk)'set @the_order='ASC'set @page_size=10set @current_page=1-----------*/--选出聚合比较的两个量和主表主键select @the_sql='create table #tem1 (sn char(11) primary key,ck int,rk int) insert into #tem1 select '+@col_name+' ,avg('+@pri_sum_col+') as ck,sum('+@sec_sum_col+') as rk from '+@table_name+' as t1,'+@sec_table_name+' as t2 where 1=1 '+@the_where +' and t1.'+@col_name+'=t2.'+@sec_col_name+' group by '+@col_name+';'--分页if @the_order='ASC'--升beginselect @the_sql=@the_sql+'SELECT * FROM ( SELECT TOP '+str(@page_size)+' temp_ck.*,ck,rk,(ck-rk) AS ck_rk FROM '+@table_name+' as temp_ck,#tem1 as tem_1 WHERE ( tem_1.sn=temp_ck.char_CKWZJL_SN and '+@col_name+' <= (SELECT MAX('+@col_name+') FROM (SELECT TOP '+str(@page_size*@current_page)+' '+@col_name+' FROM '+@table_name+' as temp_ck,#tem1 as tem_1 WHERE tem_1.sn=temp_ck.char_CKWZJL_SN '+@the_where+' ORDER BY '+@order_TYPE+' ASC) AS TT1)) '+@the_where+' ORDER BY '+@order_TYPE+' DESC ) as T2ORDER BY '+@order_TYPE+' ASC'endelse--降beginselect @the_sql=@the_sql+'SELECT * FROM (SELECT TOP '+str(@page_size)+' temp_ck.*,ck,rk,(ck-rk) AS ck_rk FROM '+@table_name+' as temp_ck,#tem1 as tem_1 WHERE ( tem_1.sn=temp_ck.char_CKWZJL_SN and '+@col_name+' >= (SELECT MIN('+@col_name+') FROM (SELECT TOP '+str(@page_size*@current_page)+' '+@col_name+' FROM '+@table_name+' as temp_ck,#tem1 as tem_1 WHERE tem_1.sn=temp_ck.char_CKWZJL_SN '+@the_where+' ORDER BY '+@order_TYPE+' DESC) AS TT1)) '+@the_where+' ORDER BY '+@order_TYPE+' ASC ) as T2ORDER BY '+@order_TYPE+' DESC'endEXEC(@the_sql)--select @the_sqlGO