oracle的decode()+nvl()的使用

 

1.decode()函数，decode(t1.state,'0',1,0) ，如果t1.state='0'返回1，否则返回0；相当于if()-else{}

 

2.nvl()函数，NVL( string1, replace_with)

　　功能：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数的都为NULL ，则返回NULL。

     例如：nvl(sum(t1.money),0)，如果t1.money为null，那么就返回0

 

3.业务查询统计的 sql：

select count(*) as recordTotal,nvl(sum(decode(t1.state,'0',1,0)),0) as successTotal,
nvl(sum(t1.money),0) as moneyTotal,
nvl(sum(decode(t1.state,'0',t1.money,0)),0) as successMoneyTotal,
nvl(sum(decode(t1.state,'0',t1.commission,0)),0) as commissionTotal 
from T_PHONE_SALE t1 where 1=1 and t1.state='0' and t1.bussiness_id='15104667195' 
and t1.create_date>=to_date('2011-07-27 11:49','yyyy-MM-dd HH24:MI') 
and t1.create_date<=to_date('2011-08-26 23:59','yyyy-MM-dd HH24:MI');