# 转载：Oracle CASE WHEN 用法介绍

Oracle 作者：shit_ka 时间：2013-12-08 13:00:51 0 删除 编辑

eshizhan>

http://www.cnblogs.com/eshizhan/archive/2012/04/06/2435493.html>

# 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 "其他" END
```
>

### 2. CASE WHEN 在语句中没有同位置的用法

#### 2.1 SELECT CASE WHEN 用法

>
```SELECT   grade, COUNT (CASE WHEN sex = 1 THEN 1
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;
```
>

### 3.关于IF-THEN-ELSE的其他实现

#### 3.1 DECODE() 函数

```select decode(sex, "M", "Male", "F", "Female", "Unknown")
from   employees;
```

#### 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%")
```

<!-- 正文结束 -->

• 博文量
1
• 访问量
1193