例子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@]