日期:2014-5-17 浏览次数:20067次

根据条件 进行group by 分组
最近有份代码原来写的意思大概如下
如果条件1存在,使用资金代码(zjdm)进行分组
如果条件2存在,使用交易代码(jydm)进行分组
如果条件1,2都存在,使用资金代码(zjdm)和交易代码(jydm)进行分组
SQL code

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