ORACLE CASE WHEN分类

当需要统计一个表中的不同状态的记录数，这时候可以用到CASE WHEN。

select decode(status,'0','成功','1','失败') status count(status),sum(case when status=0 then 1 else 0 end) as success,

sum(case when status=1 then 1 else 0 end) as error from tabletest group by status;

 

举例：

 select nvl(t.count,0) count,nvl(t.successcount,0) successcount,nvl(t.errorcount,0) errorcount,tspc.linkman linkman,tspc.service_provider_name name from TS_SERVICE_PROVIDER_CONTACT tspc left join  (select tssl.serviceprovider sid, count(tssl.serviceprovider) count,sum(case when tssl.errorcode=0 then 1 else 0 end) as successcount,sum(case when tssl.errorcode>0 then 1 else 0 end) as errorcount from t_sms_send_log tssl where tssl.serviceprovider is not null and  to_char(tssl.calltime,'yyyy/mm/dd')=to_char(trunc(sysdate)-1,'yyyy/mm/dd') group by tssl.serviceprovider ) t  on t.sid=tspc.service_provider_id where tspc.status=0;

 

当需要将不同行归并为一行的时候，也能使用case when

select max(name1) name1,max(name2) name2  from (

select case when id=1 then name end as 'name1',

case when id=2 then name end as 'name2'

)