根据条件 进行group by 分组
最近有份代码原来写的意思大概如下
如果条件1存在，使用资金代码(zjdm)进行分组
如果条件2存在，使用交易代码(jydm)进行分组
如果条件1，2都存在，使用资金代码(zjdm)和交易代码(jydm)进行分组
declare @condition1 int ,@condition2 intdeclare @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 = 1insert @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 endif @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 endif @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 endif @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 group by col1,col2,col3,zjdm,jydm endselect * from @mrmcb_sum
-- 方案1 (失败) group by 不能拆开 设置condition1 = 1,@condition2 = 1 是结果错误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 begin insert @mrmcb_cale select col1,col2,col3,max(zljg) as jg,sum(zlsl) as sl,sum(zlje) as je,'' as zjdm,'' as jydm from @zlb group by col1,col2,col3 endif @condition1 = 1begin insert @mrmcb_cale select col1,col2,col3,max(zljg) as jg,sum(zlsl) as sl,sum(zlje) as je,'' as zjdm,'' as jydm from @zlb group by col1,col2,col3,zjdm endif @condition2 = 0 begin insert @mrmcb_cale select col1,col2,col3,max(jg) as jg,sum(sl) as sl,sum(je) as je,'' as zjdm,'' as jydm from @mrmcb group by col1,col2,col3 endif @condition2 = 0 begin insert @mrmcb_cale select col1,col2,col3,max(jg) as jg,sum(sl) as sl,sum(je) as je,'' as zjdm,jydm from @mrmcb group by col1,col2,col3,jydm endif @condition1 = 0 and @condition2 = 0begin insert @mrmcb_sum select col1,col2,col3,max(jg),sum(sl),sum(je),'','' from @mrmcb_cale group by col1,col2,col3endif @condition1 = 1 and @condition2 = 0begin insert @mrmcb_sum select col1,col2,col3,max(jg),sum(sl),sum(je),zjdm,'' from @mrmcb_cale group by col1,col2,col3,zjdm endif @condition1 = 0 and @condition2 = 1begin insert @mrmcb_sum select col1,col2,col3,max(jg),sum(sl),sum(je),'',jydm from @mrmcb_cale group by col1,col2,col3,jydm endif @condition1 = 1 and @condition2 = 1begin insert @mrmcb_sum select col1,col2,col3,max(jg),sum(sl),sum(je),zjdm,jydm from @mrmcb_cale group by col1,col2,col3,zjdm,jydm endselect * from @mrmcb_sum
--方案2 (优化的还是不好)select @condition1 = 1,@condition2 = 0 --设置条件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 ;with a as ( select col1,col2,col3,max(zljg) as jg,sum(zlsl) as sl,sum(zlje) as je,'' as zjdm,'' as jydm from @zlb group by col1,col2,col3 union select col1,col2,col3,max(jg) as jg,sum(sl) as sl,sum(je) as je,'' as zjdm,'' as jydm from @mrmcb group by col1,col2,col3 ) insert @mrmcb_sum select col1,col2,col3,max(jg),sum(sl),sum(je),'' as jydm,'' as zjdm from a group by col1,col2,col3 endif @condition1 = 1 and @condition2 = 0 begin ;with a as ( select col1,col2,col3,max(zljg) as jg,sum(zlsl) as sl,sum(zlje) as je, zjdm,'' as jydm from @zlb group by col1,col2,col3,zjdm union select col1,col2,col3,max(jg) as jg,sum(sl) as sl,sum(je) as je, zjdm,'' as jydm from @mrmcb group by col1,col2,col3,zjdm ) insert @mrmcb_sum select col1,col2,col3,max(jg),sum(sl),sum(je), zjdm,'' as jydm from a group by col1,col2,col3,zjdm endif @condition1 = 0 and @condition2 = 1 begin ;with a as ( select col1,col2,col3,max(zljg) as jg,sum(zlsl) as sl,sum(zlje) as je,'' as zjdm,jydm from @zlb group by col1,col2,col3,jydm union select col1,col2,col3,max(jg) as jg,sum(sl) as sl,sum(je) as je,'' as zjdm,jydm from @mrmcb group by col1,col2,col3,jydm ) insert @mrmcb_sum select col1,col2,col3,max(jg),sum(sl),sum(je), '' as zjdm,jydm from a group by col1,col2,col3,jydm endif @condition1 = 1 and @condition2 = 1 begin ;with a as ( select col1,col2,col3,max(zljg) as jg,sum(zlsl) as sl,sum(zlje) as je,zjdm,jydm from @zlb group by col1,col2,col3,zjdm,jydm union select col1,col2,col3,max(jg) as jg,sum(sl) as sl,sum(je) as 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 a group by col1,col2,col3,zjdm,jydm endselect * from @mrmcb_sum
