結構化查詢語言/SELECT：CASE 表示式

有時需要將儲存的值（或要儲存的值）從一種表示形式轉換為另一種表示形式。假設有一個名為 status 的列，其合法值從 0 到 9，但終端使用者應該收到簡短解釋數字值的字串，例如：'ordered'、'delivered'、'back delivery'、'out of stock'，... 。推薦的方法是使用一個單獨的表，將數字值對映到解釋性字串。儘管如此，應用程式開發人員可能更喜歡在應用程式伺服器中找到解決方案。

CASE 表示式（在本頁展示）是一種技術，用於解決在 SELECT、INSERT 或 UPDATE 命令中描述的情況，以及解決其他問題。作為語言的一部分，它是一個強大的術語，可以應用在 SQL 命令中的許多地方。在本頁中，我們將重點關注它與 SELECT 命令一起使用的情況。在 INSERT 和 UPDATE 中，CASE 的策略和語法是等效的，並在那裡進行了介紹。與推薦的為翻譯使用單獨表的技術相比，CASE 表示式更加靈活（並非在所有情況下都是優勢）。

兩個示例

-- Technical term: "simple case" 
-- Select id, contact_type in a translated version and contact_value
SELECT id,
       CASE contact_type
         WHEN 'fixed line' THEN 'Phone'
         WHEN 'mobile'     THEN 'Phone'
         ELSE                   'Not a telephone number'
       END AS 'contact_type',
       contact_value
FROM   contact;

CASE 表示式以其關鍵字 CASE 作為開頭，一直延續到 END 關鍵字。在這個第一個例子中，它指定了一個列名和一系列 WHEN/THEN 子句，以及一個可選的 ELSE 子句。依次比較和評估 WHEN/THEN 子句，並與命名列的值進行比較。如果沒有任何命中，則應用 ELSE 子句。如果沒有 ELSE 子句，並且沒有任何 WHEN/THEN 子句命中，則將應用 NULL 特殊標記。

列的值與 WHEN/THEN 子句中的固定值之間的比較僅透過 "="（等於）進行。這是一個良好的起點，但實際應用需要更多內容。因此，存在 CASE 的變體。

-- Technical term: "searched case" 
-- Select persons name, weight and a denomination of the weight 
SELECT firstname,
       lastname,
       weight,
       CASE
         WHEN (weight IS NULL OR weight = 0) THEN 'weight is unknown'
         WHEN weight < 40                    THEN 'lightweight'
         WHEN weight BETWEEN 40 AND 85       THEN 'medium'
         ELSE                                     'heavyweight'
       END AS 'weight'
FROM   person;

關鍵點是 CASE 和 WHEN 兩個關鍵字的直接銜接。它們之間沒有任何列名。在此變體中，在每個 WHEN 和 THEN 之間必須有一個完整的表示式，該表示式評估為三個值邏輯項之一：true、false 或 unknown。現在可以像 WHERE 子句中已知的那樣，使用所有比較和布林運算子。甚至可以比較不同的列或函式呼叫。

語法

存在兩種變體：簡單 CASE 和 搜尋 CASE。

-- "simple case" performs successive comparisons using the equal operator: <column_name> = <expression_x>
CASE <column_name>
  WHEN <expression_1> THEN <result_1>
  WHEN <expression_2> THEN <result_2>
  ...
  ELSE                     <default_result>  -- optional
END

-- "searched case" is recognised by 'nothing' between CASE and first WHEN
CASE
  WHEN <condition_1> THEN <result_1>
  WHEN <condition_2> THEN <result_2>
  ...
  ELSE                    <default_result>  -- optional
END

簡單 CASE 僅限於一列和等號運算子的使用，而搜尋 CASE 可以使用任意運算子、函式或謂詞來評估（中間）結果的任意列。

典型用例

CASE 表示式的使用並不侷限於投影（SELECT 和 FROM 之間的列列表）。由於該子句會評估為一個值，因此可以將其應用為 SQL 命令中多個地方的值的替代品。在下面，我們將提供一些示例。

ORDER BY 子句

按以下順序對聯絡值進行排序：所有固定電話、所有手機、所有電子郵件、所有 ICQ。在每個組內，按聯絡值排序。

SELECT *
FROM   contact
ORDER  BY
       -- a "simple case" construct as substitution for a column name
       CASE contact_type
         WHEN 'fixed line' THEN 0
         WHEN 'mobile'     THEN 1
         WHEN 'email'      THEN 2
         WHEN 'icq'        THEN 3
         ELSE                   4
       END,
       contact_value;

在下一個示例中，人員按體重類別排序，在每個類別內按姓名排序。

-- order by weight classes
SELECT firstname, lastname, weight,
       CASE
         WHEN (weight IS NULL OR weight = 0) THEN 'weight is unknown'
         WHEN weight < 40                    THEN 'lightweight'
         WHEN weight BETWEEN 40 AND 85       THEN 'medium'
         ELSE                                     'heavyweight'
       END
FROM   person
ORDER  BY
       -- a "searched case" construct with IS NULL, BETWEEN and 'less than'.
       CASE
         WHEN (weight IS NULL OR weight = 0) THEN 0
         WHEN weight < 40                    THEN 1
         WHEN weight BETWEEN 40 AND 85       THEN 2
         ELSE                                     3 
       END, lastname, firstname;

WHERE 子句

在 WHERE 子句中，可能會出現固定值或列名。CASE 表示式可以用作它們的替代品。在示例中，人員會根據其出生地的不同獲得不同的體重摺扣（將其視為理論示例）。因此，體重為 95 公斤的金先生只算 76 公斤，不屬於結果集的一部分。

SELECT *
FROM   person
WHERE  CASE
         -- Modify weight depending on place of birth.
         WHEN place_of_birth = 'Dallas'   THEN weight * 0.8
         WHEN place_of_birth = 'Richland' THEN weight * 0.9
         ELSE                                  weight
       END > 80
OR     weight < 20; -- any other condition

練習

顯示所有人員的姓氏、名字和性別。將 Larry、Tom、James、John、Elias、Yorgos、Victor 視為 'male'，
Lisa 視為 'female'，其餘所有人視為 'unknown gender'。使用 簡單 CASE 表示式。

點選檢視解決方案

SELECT firstname, lastname,
       CASE firstname
         WHEN 'Larry'   THEN 'male'
         WHEN 'Tom'     THEN 'male'
         WHEN 'James'   THEN 'male'
         WHEN 'John'    THEN 'male'
         WHEN 'Elias'   THEN 'male'
         WHEN 'Yorgos'  THEN 'male'
         WHEN 'Victor'  THEN 'male'
         WHEN 'Lisa'    THEN 'female'
         ELSE                'unknown gender'
       END
FROM   person;

使用 搜尋 CASE 表示式來解決上一個問題。

點選檢視解決方案

SELECT firstname, lastname,
       CASE
         WHEN firstname in ('Larry', 'Tom', 'James', 'John', 'Elias', 'Yorgos', 'Victor')
                                     THEN 'male'
         WHEN firstname = 'Lisa'     THEN 'female'
         ELSE                             'unknown gender'
       END
FROM   person;

顯示所有人員的姓氏、名字和分類。根據姓氏的長度對人員進行分類。如果 character_length(firstname) < 4，則將該類別命名為 'short name'，如果 < 6，則命名為 'medium length'，否則命名為 'long name'。

點選檢視解決方案

-- Hint: Some implementations use a different function name: length() or len().
SELECT firstname, lastname,
       CASE 
         WHEN CHARACTER_LENGTH(firstname) < 4 THEN 'short name'
         WHEN CHARACTER_LENGTH(firstname) < 6 THEN 'medium length'
         ELSE                                      'long name'
       END
FROM   person;

統計上一個練習中短名、中長名和長名的數量。

點選檢視解決方案

-- Hint: Some implementations use a different function name: length() or len().
SELECT SUM(CASE 
             WHEN CHARACTER_LENGTH(firstname) < 4 THEN 1
             ELSE                                      0
           END) as short_names,
       SUM(CASE 
             WHEN CHARACTER_LENGTH(firstname) between 4 and 5 THEN 1
             ELSE                                                  0
           END) as medium,
       SUM(CASE 
             WHEN CHARACTER_LENGTH(firstname) > 5 THEN 1
             ELSE                                      0
           END) as long_names
FROM   person;

SELECT：集合操作

結構化查詢語言
SELECT：CASE 表示式

SELECT：子查詢