oracle中decode和case的使用例子

decode是很不错的条件函数，但是它却不能使用范围来进行条件区分，而case则刚好弥补了这一缺点，以下是二者的例子比较：

select pay_name,subgate,
sum(decode(floor(order_amount/10000),0,order_amount,0)) as  one_sum,
sum(decode(floor(order_amount/10000),0,1,0)) as one_count,
sum(decode(floor(order_amount/10000),1,order_amount,0)) as one_two_sum,
sum(decode(floor(order_amount/10000),1,1,0)) as one_two_count,
sum(decode(floor(order_amount/10000),2,order_amount,0)) as  two_three_sum,
sum(decode(floor(order_amount/10000),2,1,0)) as two_three_count,
sum(decode(floor(order_amount/10000),3,order_amount,4,order_amount,0)) as three_five_sum,
sum(decode(floor(order_amount/10000),3,1,4,1,0)) as three_five_count,
sum(decode(floor(order_amount/10000),5,order_amount,6,order_amount,7,

order_amount,8,order_amount,9,order_amount,0)) as five_ten_sum,
sum(decode(floor(order_amount/10000),5,1,6,1,7,1,8,1,9,1,0)) as five_ten_count,
sum(decode(floor(order_amount/100000),0,0,order_amount)) as ten_sum,
sum(decode(floor(order_amount/100000),0,0,1)) as ten_count

from order_tab
where order_date>=to_date('20061201','yyyymmdd')
and order_dateand order_status=30
and lower(pay_name) in ('chinapay','cbj','abc','icbc','cbc','cmb')
group by pay_name,subgate
order by pay_name,subgate;

select sum(case when count_01 <=10 then 1 else 0 end) as le10,
sum(case when count_01 >10 and count_01 <=20 then 1 else 0 end) as le20,
sum(case when count_01 >20 and count_01 <=50 then 1 else 0 end) as le50,
sum(case when count_01 >50 and count_01 <=100 then 1 else 0 end) as le100,
sum(case when count_01 >100 and count_01 <=200 then 1 else 0 end) as le200,
sum(case when count_01 >200 and count_01 <=500 then 1 else 0 end) as le500,
sum(case when count_01 >500 and count_01 <=1000 then 1 else 0 end) as le1000,
sum(case when count_01 >1000 then 1 else 0 end) as g1000
from (select ssn,sum(t.changepoints + t.fpoints) count_01
 from pay_detail_new_200701 t
 where serviceip not in ('charge','ptransfer')
 group by t.ssn);

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

转载于:http://blog.itpub.net/16566331/viewspace-678408/