oracle case nvl,oracle中的nvl 、nvl2、decode、case when 函數的用法

參考文檔：http://www.cnblogs.com/eshizhan/archive/2012/04/06/2435493.html

-- nvl函數

--　NVL( string1, replace_with)　　功能：如果string1為NULL，則NVL函數返回replace_with的值，否則返回string1的值。

select nvl(flag,'11')flag from t_operation_log

-- nvl2函數

-- 　NVL2(E1, E2, E3)的功能為：如果E1為NULL，則函數返回E3，若E1不為null，則返回E2。

select nvl2(flag,'11','22')flag from t_operation_log

-- decode 函數

--decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,缺省值)

select flag,decode(flag,'1','成功','0','失敗') flagchina from t_operation_log

case when 函數

--第一中寫法

select flag, (

case flag when '1' then '成功'

when '0' then '失敗'

else '其它'

end ) chinaflag

from t_operation_log

-- 第二種寫法

select flag, (

case when flag='1' then '成功'

when flag='0' then '失敗'

else '其它'

end

) chinaflag from t_operation_log

CASE WHEN 在語句中不同位置的用法

2.1 SELECT CASE WHEN 用法

SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1 /*sex 1為男生，2位女生*/

ELSE NULL

END) 男生數,

COUNT (CASE WHEN sex = 2 THEN 1

ELSE NULL

END) 女生數

FROM students GROUP BY grade;

2.3 WHERE CASE WHEN 用法

SELECT T2.*, T1.*

FROM T1, T2

WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND

T1.SOME_TYPE LIKE 'NOTHING%'

THEN 1

WHEN T2.COMPARE_TYPE != 'A' AND

T1.SOME_TYPE NOT LIKE 'NOTHING%'

THEN 1

ELSE 0

END) = 1

2.4 GROUP BY CASE WHEN 用法

SELECT

CASE WHEN salary <= 500 THEN '1'

WHEN salary > 500 AND salary <= 600 THEN '2'

WHEN salary > 600 AND salary <= 800 THEN '3'

WHEN salary > 800 AND salary <= 1000 THEN '4'

ELSE NULL END salary_class, --別名命名COUNT(*)

FROM Table_A

GROUP BY

CASE WHEN salary <= 500 THEN '1'

WHEN salary > 500 AND salary <= 600 THEN '2'

WHEN salary > 600 AND salary <= 800 THEN '3'

WHEN salary > 800 AND salary <= 1000 THEN '4'

ELSE NULL END;