参考文档:/eshizhan/archive//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.1SELECTCASE 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.3WHERECASE WHEN 用法
SELECT T2.*, T1.*
FROM T1, T2
WHERE (CASE WHEN PARE_TYPE = 'A' AND
T1.SOME_TYPE LIKE 'NOTHING%'
THEN 1
WHEN PARE_TYPE != 'A' AND
T1.SOME_TYPE NOT LIKE 'NOTHING%'
THEN 1
ELSE 0
END) = 1
2.4GROUP BYCASE 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;