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@]
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/