使用with处理报表处理一例

with sum_obj as(
    select
        nvl(county_code,'合计') county_code,
        sum(case when color_flag = 0 then objective else 0 end) t_red,
        sum(case when color_flag = 3 then objective else 0 end) t_yellow,
        sum(objective) t_total,
        sum(case when kind = 1 and color_flag = 0 then objective else 0 end) a_red,
        sum(case when kind = 1 and color_flag = 3 then objective else 0 end) a_yellow,
        sum(case when kind = 1 then objective else 0 end) a_total,
        sum(case when kind = 2 and color_flag = 0 then objective else 0 end) b_red,
        sum(case when kind = 2 and color_flag = 3 then objective else 0 end) b_yellow,
        sum(case when kind = 2 then objective else 0 end) b_total
    from tjw_core_bind_objective
    group by rollup(county_code)
  ),
  sum_real as (
      select /*+full(b)*/
        nvl(county_code,'合计') county_code,
        sum(case when b.march_flag = 0 then 1 else 0 end) t_red,
        sum(case when b.march_flag = 3 then 1 else 0 end) t_yellow,
        sum(1) t_total,
        sum(case when b.kind = 1 and b.march_flag = 0 then 1 else 0 end) a_red,
        sum(case when b.kind = 1 and b.march_flag = 3 then 1 else 0 end) a_yellow,
        sum(case when b.kind = 1 then 1 else 0 end) a_total,
        sum(case when b.kind = 2 and b.march_flag = 0 then 1 else 0 end) b_red,
        sum(case when b.kind = 2 and b.march_flag = 3 then 1 else 0 end) b_yellow,
        sum(case when b.kind = 2 then 1 else 0 end) b_total
    from tjw_core_bind_final a,tjw_core_bind_5 b
    where a.user_id = b.user_id
    and a.color_flag in (1,2)
    and b.march_flag in (0,3)
    group by rollup(county_code)
  )
  select '所有用户',a.county_code,
      a.t_red,a.t_red - b.t_red,trunc((a.t_red - b.t_red)/a.t_red * 100, 2 ) t_red_ratio,
    a.t_yellow,a.t_yellow - b.t_yellow,trunc((a.t_yellow - b.t_yellow)/a.t_yellow * 100, 2 ) t_yellow_ratio,
    a.t_total,a.t_total - b.t_total,trunc((a.t_total - b.t_total)/a.t_total * 100, 2 ) t_total_ratio
  from sum_obj a,sum_real b
  where a.county_code = b.county_code
  union all
  select '客户经理',a.county_code,
      a.a_red,a.a_red - b.a_red,trunc((a.a_red - b.a_red)/a.a_red * 100, 2 ) a_red_ratio,
    a.a_yellow,a.a_yellow - b.a_yellow,trunc((a.a_yellow - b.a_yellow)/a.a_yellow * 100, 2 ) a_yellow_ratio,
    a.a_total,a.a_total - b.a_total,trunc((a.a_total - b.a_total)/a.a_total * 100, 2 ) a_total_ratio
  from sum_obj a,sum_real b
  where a.county_code = b.county_code
  union all
  select '电话经理',a.county_code,
      a.b_red,a.b_red - b.b_red,trunc((a.b_red - b.b_red)/a.b_red * 100, 2 ) b_red_ratio,
    a.b_yellow,a.b_yellow - b.b_yellow,trunc((a.b_yellow - b.b_yellow)/a.b_yellow * 100, 2 ) b_yellow_ratio,
    a.b_total,a.b_total - b.b_total,trunc((a.b_total - b.b_total)/a.b_total * 100, 2 ) b_total_ratio
  from sum_obj a,sum_real b
  where a.county_code = b.county_code;

以前是直接使用union all将基础表跑几遍, 现在这样详单于中间中间一个临时表存储统计结果, 再在结果表的基础上对数据进行处理. 当然方便了很多了^_^
[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-795129/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94317/viewspace-795129/