group by带转换的情况

1.脚本分析

group by分组时，要求某些科室转换成另外一个科室，可在group by子句分组时，使用case when进行转换，同时select子句也要有相同的转换

  select  OUT_DEPT_NAME,
          OUT_NUM,
          TOT_COST,
         sj_percent_cost,
         HS_NUM,
         HS_COST,
         OPS_ALL_COST, 
         hs_percent_cost, 
         dept_kzbz,    
         up_dept_kzbz,   
         up_percent_dept_kzbz,
         (case when up_percent_dept_kzbz>=0
                    and up_percent_dept_kzbz<0.1
              then 0
              when up_percent_dept_kzbz>=0.1
                   and up_percent_dept_kzbz<0.2
              then up_dept_kzbz*0.5
              when up_percent_dept_kzbz>=0.2
                   and up_percent_dept_kzbz<0.3
              then up_dept_kzbz*0.6
              when up_percent_dept_kzbz>=0.3 
              then up_dept_kzbz*0.7 
                else  up_dept_kzbz
                  end) kz_percent_dept_kzbz, 
         DRUG_COST,
         sj_percent_drug_cost,
          HS_DRUG_COST,
         OPS_DRUG_COST,
         drug_kzbz, 
         hs_percent_drug_cost,
         hs_over_percent_drug_cost

from (
  select nvl((select x.dept
             from neubi_work.dim_dept x
            where x.dept_code = t.out_dept_code),
           '其它') OUT_DEPT_NAME,
       OUT_NUM,
       TOT_COST,
       decode(OUT_NUM, 0, 0, TOT_COST / OUT_NUM) sj_percent_cost,
       HS_NUM,
       HS_COST,
       OPS_COST+OPS_DRUG_COST                  OPS_ALL_COST, 
       decode(HS_NUM,0,0,(HS_COST-OPS_COST-OPS_DRUG_COST)/HS_NUM) hs_percent_cost, 
       nvl((select KZ_COST
             from neubi_work.dim_CITYYB_COST x
            where x.dept_code=t.out_dept_code),
           0)                                         dept_kzbz,      
       ((decode(HS_NUM,0,0,(HS_COST-OPS_COST-OPS_DRUG_COST)/HS_NUM))-
       (nvl((select KZ_COST
               from neubi_work.dim_CITYYB_COST x
              where x.dept_code = t.out_dept_code),
             0)))*HS_NUM                              up_dept_kzbz,   
       decode(HS_COST,
              0,
              0,
              (((decode(HS_NUM,0,0,(HS_COST-OPS_COST-OPS_DRUG_COST)/HS_NUM)) -
              (nvl((select KZ_COST
                       from neubi_work.dim_CITYYB_COST x
                      where x.dept_code = t.out_dept_code),
                     0))) * HS_NUM)/HS_COST)          up_percent_dept_kzbz,
       DRUG_COST,
       decode(TOT_COST,0,0,DRUG_COST/TOT_COST)        sj_percent_drug_cost,
       HS_DRUG_COST,
       OPS_DRUG_COST,
       nvl((select DRUG_KZ_COST
             from neubi_work.dim_CITYYB_COST x
            where x.dept_code = t.out_dept_code),
           0)                                         drug_kzbz, 
       (case
         when (HS_COST-OPS_COST-OPS_DRUG_COST) <= 0 then
          0
         else
          (HS_DRUG_COST-OPS_DRUG_COST)/(HS_COST-OPS_COST-OPS_DRUG_COST)
               end)                                    hs_percent_drug_cost,
       ((case
         when (HS_COST-OPS_COST-OPS_DRUG_COST)<=0 then
          0
         else
          (HS_DRUG_COST-OPS_DRUG_COST)/(HS_COST-OPS_COST-OPS_DRUG_COST)
         end) - nvl((select DRUG_KZ_COST
             from neubi_work.dim_CITYYB_COST x
            where x.dept_code = t.out_dept_code),
           0)) *(HS_DRUG_COST-OPS_DRUG_COST)            hs_over_percent_drug_cost
  from (
  
  
  select       OUT_DEPT_CODE,
               sum(OUT_NUM) OUT_NUM,
               sum(TOT_COST) TOT_COST,
               sum(HS_NUM) HS_NUM,
               sum(HS_COST) HS_COST,
               sum(OPS_COST) OPS_COST,
               sum(DRUG_COST) DRUG_COST,
               sum(HS_DRUG_COST) HS_DRUG_COST,
               sum(OPS_DRUG_COST) OPS_DRUG_COST
       
     from (    
                  select 
                       (case when t.dept_code in ('1096','1095')
                                then '1286'
                                when t.dept_code='1261'
                                then '1017'
                                else t.dept_code
                                  end)                out_dept_code, 
                          (case when t.dept_code in ('1096','1095')
                                then '妇科病区'
                                when t.dept_code='1261'
                                then '腺体外科病区'  
                                else t.dept_name
                                  end)               dept_name,
                       t.name                        name,      
                       count(distinct h.invoice_no)   OUT_NUM,    
                       sum(h.tot_cost)                TOT_COST,
                       count(distinct(CASE
                                        WHEN t.INPATIENT_NO  IN
                                             (select INPATIENT_NO  from INH_ONEICDYB_MID T
                                                WHERE T.PACT_CODE='2'
                                              union all
                                              select INPATIENT_NO  from INH_ONEICDCOST_MID  T 
                                             union all
                                              select INPATIENT_NO from INH_SHIYB_MID T  
                                               ) THEN
                                         null
                                        ELSE
                                         h.invoice_no
                                      END))           HS_NUM,
                        nvl(sum(case
                             when t.INPATIENT_NO  IN
                                  (select INPATIENT_NO   from INH_ONEICDYB_MID T
                                      WHERE T.PACT_CODE='2'
                                    union all
                                   select INPATIENT_NO  from INH_ONEICDCOST_MID  T
                                    union all
                                    select INPATIENT_NO  from INH_SHIYB_MID T
                                    )then
                              0
                             else
                              h.tot_cost
                                        end),0)                 HS_COST,  
                        nvl((select sum(b.tot_cost)
                                from inh_pay b
                               where b.inpatient_no = t.inpatient_no
                                 and b.EXEC_DPCD = '1092'
                                 and b.drug_flag=0  ),0)  OPS_COST, 
                        nvl((select sum(b.tot_cost)
                                from inh_fin_ipb_feeinfo b
                               where b.inpatient_no = t.inpatient_no
                                 and b.fee_code in ('001', '002', '003') ),0) DRUG_COST, 
                       nvl((select sum(b.tot_cost)
                                from inh_fin_ipb_feeinfo b
                               where b.inpatient_no = t.inpatient_no
                                 and b.fee_code in ('001', '002', '003') 
                                 and t.inpatient_no not in
                                  (select INPATIENT_NO  from INH_ONEICDYB_MID T
                                      WHERE T.PACT_CODE='2'
                                   union all
                                  select INPATIENT_NO  from INH_ONEICDCOST_MID  T   
                                    union all
                                    select INPATIENT_NO  from INH_SHIYB_MID T) 
                                                     ),0)                     HS_DRUG_COST, 
                        nvl((select sum(e.tot_cost)
                              from inh_pay e
                             where e.inpatient_no = t.inpatient_no
                               and (e.RECIPE_DPCD = '1092' or
                                          e.EXEC_DPCD = '1092')
                               and e.drug_flag=1 ),0) OPS_DRUG_COST  
                           
                  from inh_ipr_inmaininfo_new   t,    
                       inh_ipr_siinmaininfo s,
                       inh_IPB_BALANCEHEAD  h   
                 WHERE t.inpatient_no = s.inpatient_no
                   and s.invoice_no = h.invoice_no  
                   and s.person_type not in ('32', '31') 
                   and h.pact_code = '2'      
                   and h.waste_flag = '1' 
                   and t.dept_code not in
                   ( '1115',
                    '1116',
                    '1074',
                    '1083',
                    '1084',
                    '1085', 
                    '1086',
                    '1087',
                    '1088',
                    '1117',
                    '1071',
                    '1003',
                    '1004',
                    '1005',
                    '1006',
                    '1007',
                    '1008',
                    '1073',
                    '1118',
                    '1075',
                    '1235',
                    '1079',
                    '1080',
                    '1010',
                    '1011',
                    '1013',
                    '1014',
                    '1015',
                    '1092',
                    '1016',
                    '1289',
                    '1082',
                    '1077',
                    '1081',
                    '1111',
                    '1119',
                    '1101' 
                   ) 
                   and trunc(h.balance_date) >= to_date($begin_time, 'YYYY-MM-DD HH24:MI:SS')
                   AND trunc(h.balance_date) <=to_date($end_time, 'YYYY-MM-DD HH24:MI:SS')
                 group by (case when t.dept_code in ('1096','1095')
                                then '1286'
                                when t.dept_code='1261'
                                then '1017'
                                else t.dept_code
                                  end),    
                          (case when t.dept_code in ('1096','1095')
                                then '妇科病区'
                                when t.dept_code='1261'
                                then '腺体外科病区'  
                                else t.dept_name
                                  end),  
                          t.inpatient_no, 
                          t.name 
                      )
          group by OUT_DEPT_CODE
                    ) t 
        )m

2.总结