Oracle的CASE表达式

现在Oracle支持两种CASE表示方式，一种Oracle称为simple CASE(简单形式),另外一种为searched CASE(查询形式)。simple CASE相当于使用函数Decode，但是Case一般用于操作更为复杂的语句。CASE的形式更易于阅读。

simple CASE的表示形式为：

CASE expr WHEN comparison_expr THEN return_expr

[, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END

*注：[]里面的内容可以省略

执行方式为：

Oracle检查表达式expr是否和表达式comparison_expr相等，如果相等则执行return_expr，否则执行ELSE else_expr的内容。

searched CASE表示形式为：

CASE WHEN condition THEN return_expr [, WHEN condition THEN return_expr]

... ELSE else_expr] END

可以使用任何一种方式。

这两种表示方式都是最多支持255个参数，其中每对When...Then算作2个参数。

例如：

1、

SELECT AVG(CASE when e.salary > 2000 THEN e.salary ELSE 2000 end)

AS avg_sal_2k_floor

FROM employees e;

2、

SELECT AVG(CASE WHEN e.sal BETWEEN 0 AND 2000 THEN e.sal ELSE null END) avg2000,

AVG(CASE WHEN e.sal BETWEEN 2001 AND 5000 THEN e.sal ELSE null END) avg5000

FROM emps e;

3、

SELECT SUM(CASE WHEN cust_credit_limit BETWEEN 0 AND 3999 THEN 1 ELSE 0 END)

AS "0-3999",

SUM(CASE WHEN cust_credit_limit BETWEEN 4000 AND 7999 THEN 1 ELSE 0 END)

AS "4000-7999",

SUM(CASE WHEN cust_credit_limit BETWEEN 8000 AND 11999 THEN 1 ELSE 0 END)

AS "8000-11999",

SUM(CASE WHEN cust_credit_limit BETWEEN 12000 AND 16000 THEN 1 ELSE 0 END)

AS "12000-16000"

FROM customers WHERE cust_city = 'Marshal';

0-3999 4000-7999 8000-11999 12000-16000

---------- ---------- ---------- -----------

8 7 7 1

4、

SELECT (CASE WHEN cust_credit_limit BETWEEN 0 AND 3999 THEN ' 0 - 3999'

WHEN cust_credit_limit BETWEEN 4000 AND 7999 THEN ' 4000 - 7999'

WHEN cust_credit_limit BETWEEN 8000 AND 11999 THEN ' 8000 - 11999'

WHEN cust_credit_limit BETWEEN 12000 AND 16000 THEN '12000 - 16000' END)

AS BUCKET, COUNT(*) AS Count_in_Group

FROM customers WHERE cust_city = 'Marshal' GROUP BY

(CASE WHEN cust_credit_limit BETWEEN 0 AND 3999 THEN ' 0 - 3999'

WHEN cust_credit_limit BETWEEN 4000 AND 7999 THEN ' 4000 - 7999'

WHEN cust_credit_limit BETWEEN 8000 AND 11999 THEN ' 8000 - 11999'

WHEN cust_credit_limit BETWEEN 12000 AND 16000 THEN '12000 - 16000' END);

BUCKET COUNT_IN_GROUP

------------- --------------

0 - 3999 8

4000 - 7999 7

8000 - 11999 7

12000 - 16000 1