注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

帅小伙的博客

希望能在这里交到更多的朋友

 
 
 

日志

 
 
 
 

oracle中decode和case的使用例子  

2007-03-30 16:09:10|  分类: oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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_date<to_date('20070201','yyyymmdd')
and 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);

感谢lisa提供的case例子!

 

  评论这张
 
阅读(437)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018