Oracle CASE WHEN 用法介绍

1. CASE WHEN 表达式有两种形式--简单Case函数 CASE sex
WHEN "1" THEN "男"
WHEN "2" THEN "女"
ELSE "其他" END --Case搜索函数 CASE
WHEN sex = "1" THEN "男"
WHEN sex = "2" THEN "女"
ELSE "其他" END2. 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.2 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） = 12.3 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;3.关于IF-THEN-ELSE的其他实现

3.1 DECODE（）函数select decode（sex, "M", "Male", "F", "Female", "Unknown"）
from employees;貌似只有Oracle提供该函数，而且不支持ANSI SQL，语法上也没CASE WHEN清晰，个人不推荐使用。

3.2 在WHERE中特殊实现

SELECT T2.*, T1.*
FROM T1, T2
WHERE （T2.COMPARE_TYPE = "A" AND T1.SOME_TYPE LIKE "NOTHING%"）
OR
（T2.COMPARE_TYPE ！= "A" AND T1.SOME_TYPE NOT LIKE "NOTHING%"）这种方法也是在特殊情况下使用，要多注意逻辑，不要弄错。--------------------------------------------------------------------------------Linux-6-64下安装Oracle 12C笔记 http://www.linuxidc.com/Linux/2013-07/86805.htm在CentOS 6.4下安装Oracle 11gR2（x64） http://www.linuxidc.com/Linux/2014-02/97374.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htm--------------------------------------------------------------------------------更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址