Oracle Case 语法

 摘自：http://blog.csdn.net/xuanxingmin/article/details/4266327

 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

 

 

下面是 MSSQL  CASE 语法

http://blog.csdn.net/myflysun/article/details/9252173