日期:2014-05-17 浏览次数:20719 次
declare @condition1 int ,@condition2 int declare @zlb table ( col1 float, col2 float, col3 float, zljg float, zlsl float, zlje float, zjdm varchar(255), jydm varchar(255) ) declare @mrmcb table ( col1 float, col2 float, col3 float, jg float, sl float, je float, zjdm varchar(255), jydm varchar(255) ) declare @mrmcb_cale table ( col1 float, col2 float, col3 float, jg float, sl float, je float, zjdm varchar(255), jydm varchar(255) ) declare @mrmcb_sum table ( col1 float, col2 float, col3 float, jg float, sl float, je float, zjdm varchar(255), jydm varchar(255) ) select @condition1 = 1,@condition2 = 1 insert @zlb select 1,2,3,10,20,200,'买','张三' union select 1,2,3,10,30,300,'买','张三' union select 1,2,3,10,20,200,'买','李四' union select 1,2,3,30,10,300,'买','李四' insert @mrmcb select 1,2,3,20,50,1000,'买','张三' union select 1,2,3,10,20,200,'卖','张三' union select 1,2,3,10,50,500,'买','李四' union select 1,2,3,20,30,600,'卖','李四' if @condition1 = 0 and @condition2 = 0 -- 如果条件1和条件2都没有 begin insert @mrmcb_cale select col1,col2,col3,max(zljg),sum(zlsl),sum(zlje),'','' from @zlb group by col1,col2,col3 insert @mrmcb_cale select col1,col2,col3,max(jg),sum(sl),sum(je),'','' from @mrmcb group by col1,col2,col3 insert @mrmcb_sum select col1,col2,col3,max(jg),sum(sl),sum(je),'','' from @mrmcb_cale group by col1,col2,col3 end if @condition1 = 0 and @condition2 = 1 -- 如果条件2存在,使用交易代码(jydm)进行分组 begin insert @mrmcb_cale select col1,col2,col3,max(zljg),sum(zlsl),sum(zlje),'',jydm from @zlb group by col1,col2,col3,jydm insert @mrmcb_cale select col1,col2,col3,max(jg),sum(sl),sum(je),'',jydm from @mrmcb group by col1,col2,col3,jydm insert @mrmcb_sum select col1,col2,col3,max(jg),sum(sl),sum(je),'',jydm from @mrmcb_cale group by col1,col2,col3,jydm end if @condition1 = 1 and @condition2 = 0 -- 如果条件1存在,使用资金代码(zjdm)进行分组 begin insert @mrmcb_cale select col1,col2,col3,max(zljg),sum(zlsl),sum(zlje),zjdm,'' from @zlb group by col1,col2,col3,zjdm insert @mrmcb_cale select col1,col2,col3,max(jg),sum(sl),sum(je),zjdm,'' from @mrmcb group by col1,col2,col3,zjdm insert @mrmcb_sum select col1,col2,col3,max(jg),sum(sl),sum(je),zjdm,'' from @mrmcb_cale group by col1,col2,col3,zjdm end if @condition1 = 1 and @condition2 = 1 -- 如果条件1,2都存在,使用资金代码(zjdm)和交易代码(jydm)进行分组 begin insert @mrmcb_cale select col1,col2,col3,max(zljg),sum(zlsl),sum(zlje),zjdm,jydm from @zlb group by col1,col2,col3,zjdm,jydm insert @mrmcb_cale select col1,col2,col3,max(jg),sum(sl),sum(je),zjdm,jydm from @mrmcb group by col1,col2,col3,zjdm,jydm insert @mrmcb_sum select col1,col2,col3,max(jg),sum(sl),sum(je),zjdm,jydm from @mrmcb_cale