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