decode

DECODE ( expression, search, result [ , search, result ]...[, default ] )

DECODE is similar to the IF-THEN-ELSE and CASE expressions:

CASE expression
[WHEN search THEN result]
[WHEN search THEN result]

...
[ELSE default];

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)的理解如下：

if （条件==值1）then    return(翻译值1)

elsif （条件==值2）then　return(翻译值2)　　　　

......

elsif （条件==值n）then　return(翻译值n)

else　return(缺省值)

end if

示例：

SELECT product_description
       , DECODE(weight ,2, 'Light' 
                       ,50, 'Medium'
                       ,71, 'Heavy',
                       ,99, 'Call for help'
                       ,'N/A') 

相当于

 

SELECT product_description
       , case weight when 2 then 'Light' 
                     when 50 then 'Medium'
                     when 71 then 'Heavy',
                     when 99 then 'Call for help'
                     else 'N/A'
         end as weight

 

 一种计数方法：

status为active的总数

sum(decode(status,'active',1,0)) as active