一个语句整合了case when的精妙用法

统计这个玩意有时确实很烦人，但有时又很可爱，有时你只要亲切的动用case when语句，这个语句将无比强大。当然这个是在oracle强力的支持下：

select b.no,b.org_no,o.name,
   ( case when (o.upper1 is null) then ''    when (o.upper2 is null) then o.no    when (o.upper3 is null) then o.upper1    else o.upper2 en　d ) as up,org.name,
    sum(case when( h.transtype='CWD'  ) then 1 else 0 end),
    sum(case when( h.transtype='CWD'  and h.transamount is not null) then h.transamount else '0' 　　　　　　end),
    sum(case when( h.transtype='CDP'  and h.transamount is not null) then 1 else 0 end),
    sum(case when( h.transtype='CDP'  and h.transamount is not null) then h.transamount else '0' end),
    sum(case when( h.transtype='TFR'  and h.transamount is not null) then 1 else 0 end),
    sum(case when( h.transtype='TFR'  and h.transamount is not null ) then h.transamount else '0' end),
    sum(case when( h.transtype='FUL' or h.transtype='FUB' or h.transtype='FUR' ) then 1 else 0 end),
    sum(case when( h.transtype='FUL' or h.transtype='FUB' or h.transtype='FUR' and h.subamount is not null) then h.subamount else '0' 　end),
    sum(case when( (h.transtype='BTB' or h.transtype='BTF') and h.banktfrbilltype ='13') then 1 else 0 end),
    sum(case when( (h.transtype='BTB' or h.transtype='BTF') and h.banktfrbilltype ='13' and h.banktframount is not null ) then h.banktfra　mount els　　e '0' end),
    sum(case when( (h.transtype='BTB' or h.transtype='BTF') and h.banktfrbilltype ='14' ) then 1 else 0 end),
    sum(case when( (h.transtype='BTB' or h.transtype='BTF') and h.banktfrbilltype ='14' and h.banktframount is not null ) then h.banktfra　mount els　　e '0' end),
    sum(case when( h.transtype='TTB' or h.transtype='TTF') then 1 else 0 end),
    sum(case when( (h.transtype='TTB' or h.transtype='TTF') and h.thirdpartyamount is not null) then h.thirdpartyamount else '0' end),
    sum(case when( h.transtype='YDP'  ) then 1 else 0 end),sum(case when( h.transtype='YDP' and h.paymentamount is not null  ) then 　h.payment　amount else '0' end),
    sum(case when( h.transtype='LTP'  ) then 1 else 0 end),sum(case when( h.transtype='LTP' and h.paymentamount is not null  ) then 　h.paymenta　mount else '0' end),
    sum(case when( h.transtype='DXP'  ) then 1 else 0 end),sum(case when( h.transtype='DXP' and h.paymentamount is not null  ) then 　h.payment　amount else '0' end),
    sum(case when( h.transtype='MSP'  ) then 1 else 0 end)
    from hist_transtable h ,dev_base_info b,org_table o,(select no,name from org_table ) org
    where  h.devno = b.no and  substr(h.transtime,0,8) ='20091022'  and org.no(+) = ( case when (o.upper1 is null) then ''    when (o.uppe　r2 is null) t　　　　　hen o.no    when (o.upper3 is null) then o.upper1    else o.upper2 end )  and b.org_no = o.no and (o.no ='49999　99'  or ( o.upper1 ='4999　　　　　999' or 　　o.upper2='4999999' or o.upper3='4999999' or o.upper4='4999999' or o.upper5='499999　9' )) 
　group by  ( case when (o.upper1 is null) then ''    when (o.upper2 is null) then o.no    when (o.upper3 is null) then o.upper1    else o.　upper2 end ) ,org.name,b.org_no,o.name,b.no
    order by up asc,b.org_no asc,b.no asc

 

也许看着这么长的语句看着心烦，但是你会发现里面浓缩了很多case when精妙的语法.

这段sql语句里面含有case when 语句的基本应用,子查询语句，case when的表达式的与表字段的外连接.....