案例：oracle中case when的用法

例子1、最近支持同事做报表，特提供数据源的sql脚本，当中就用到了case when。主要的视图结构如下：
create or replace view v_card_channel_count5
(
icpcode,sumarea,count1201,count1202,count1203,count1204,count1205,
count1206,count1207,count1208,count1209,count1210,count1211,count1212,
count1213,count1214,count1215,count1216,count1217,count1218,count1219,count1220,
count1221,count1222,count1223,count1224,count1225,count1226,count1227,count1228,
count1229,count1230,count1231)
as
select icpcode,'当期订购数',
sum(case when to_char(subscribe_time,'dd')='01' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='02' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='03' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='04' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='05' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='06' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='07' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='08' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='09' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='10' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='11' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='12' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='13' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='14' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='15' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='16' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='17' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='18' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='19' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='20' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='21' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='22' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='23' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='24' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='25' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='26' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='27' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='28' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='29' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='30' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='31' then subscribe_num else 0 end)
from temp_card_service_subscribe
where to_char(subscribe_time,'yyyymm')='200612'
group by icpcode
union all
select icpcode,'当期资源总数',
count(distinct case when to_char(subscribe_time,'dd')='01' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='02' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='03' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='04' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='05' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='06' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='07' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='08' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='09' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='10' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='11' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='12' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='13' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='14' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='15' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='16' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='17' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='18' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='19' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='20' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='21' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='22' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='23' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='24' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='25' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='26' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='27' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='28' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='29' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='30' then contentid else null end),
count(distinct case when to_char(subscribe_time,'dd')='31' then contentid else null end)
from temp_card_service_subscribe
where to_char(subscribe_time,'yyyymm')='200612'
group by icpcode;

另外一个例子是优化sql语句的，见如下：
（原sql语句）
select f.subject_id from subject_data_common f
where validat ='A' and template_id = 1
and f.miscid in ('999')
and f.subject_id in (select subject_id
from ( select -1 as subject_id from dual
union all
select subject_id from subject_data where field_id= 3 and field_value in ('wu')
union all
select subject_id from subject_data where field_id = 4 and field_value in ('money')
union all
select subject_id from subject_data where field_id =7 and field_value in ('1')
union all
select subject_id from subject_data_common
where instr(',' || keyword || ',',',财富彩票投资,') > 0
)
group by subject_id
having count(subject_id) >= 4
)
order by f.createdate asc;

（从减少表遍历和利用索引上考虑如下改进的sql语句）
select f.subject_id from subject_data_common f,(select subject_id
from ( select -1 as subject_id from dual
union all
select case
when field_id= 3 and field_value in ('wu') then subject_id
when field_id= 4 and field_value in ('money') then subject_id
when field_id= 7 and field_value in ('1') then subject_id
else null
end
from subject_data
union all
select subject_id from subject_data_common
where instr(',' || keyword || ',',',财富彩票投资,') > 0
)
group by subject_id
having count(subject_id) >= 4) e
where validat ='A' and template_id = 1
and f.miscid in ('999')
and f.subject_id = e.subject_id
order by f.createdate asc;

[@more@]

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

转载于:http://blog.itpub.net/38542/viewspace-886096/