oracle中的nvl 、nvl2、decode、case when 函數的用法__靜態函數

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

 

<span style="font-size:18px;">-- 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</span>

case when 函數 

--第一中寫法select flag, (case flag when '1' then '成功'       when '0' then '失敗'       else '其它'       end ) chinaflagfrom 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;