根据条件 进行group by 分组最近有份代码原来写的意思大概如下如果条件1存在，使用资金代码(zjdm)进行分组
根据条件 进行group by 分组
最近有份代码原来写的意思大概如下
如果条件1存在，使用资金代码(zjdm)进行分组
如果条件2存在，使用交易代码(jydm)进行分组
如果条件1，2都存在，使用资金代码(zjdm)和交易代码(jydm)进行分组
- SQL code
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
想把这个代码缩短，优化，不喜欢看到重复的代码(强迫症。。。)下面贴2个自己优化的方法
方案1 (失败) group by 不能拆开
- SQL code
-- 方案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 (优化的还是不好，代码重复太多)
- SQL code
--方案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
请各位大神看看优化下，最好不要有重复的代码。
[解决办法]
select col1,col2,col3,max(zljg),sum(zlsl),sum(zlje),case when @condition1 = 0 then '' else zjdm end as zjdm,
case when @condition2 = 0 then '' else jydm end as jydm
from @zlb
group by col1,col2,col3,case when @condition1 = 0 then '' else zjdm end,
case when @condition2 = 0 then '' else jydm end