MySQL基礎入門之Case語句用法範例

2022-08-08 14:03:18

引言

MySQL CASE 是一個 MySQL 語句查詢關鍵字，它定義了處理迴圈概念以執行條件集並使用 IF ELSE 返回匹配案例的方式。 MySQL 中的 CASE 是一種控制語句，它驗證條件案例集，並在第一個案例滿足 else 值時顯示值並退出迴圈。

如果沒有找到 TRUE 且語句沒有 ELSE 部分或值，則 CASE 返回 NULL。基本上，CASE 語句類似於 IF THEN ELSE 邏輯迴圈語句。在 MySQL 伺服器上執行時，它會讀取條件表示式，當表示式大小寫匹配時，nit 會顯示結果。之後，它會進一步停止執行。

CASE 的語法

下面的語法定義了 CASE 語句 SQL 查詢結構：

CASE
WHEN cond1 THEN value1
WHEN cond2 THEN value2
WHEN condN THEN valueN
ELSE value
END;

Cond1,cond2,…..,condN：表示在 CASE 語句列表中需要評估的條件。
Value1, value2, .....,valueN：表示滿足條件時需要顯示的各個值。
Value：表示 else 部分滿足時顯示的值。

這部分允許在 MySQL 的查詢中新增邏輯 CASE 語句。為了生成有效的語句，我們在任何地方使用帶有 WHERE SELECT 和 ORDER BY 等子句的 CASE 語句。您可以通過如下所示的語法進行評估：

SELECT column1,column2,
CASE
WHEN cond1 THEN value1
WHEN cond2 THEN value2
WHEN condN THEN valueN
ELSE value
END
FROM TableName;

CASE 語句在 MySQL 中是如何工作的？

CASE 語句包含兩個過程：一個是 Simple CASE，另一個是 Searched CASE。我們使用 CASE 語句根據匹配條件提供結果值，使用邏輯控制方法與 SQL 子句（如 SELECT、WHERE 和 ORDER BY）一起為 SQL 查詢提供結果值。

假設，下面的查詢解釋了 CASE 邏輯部分，其中我們提到了一個像 Student 的表，其中包含欄位 StudentName、State、City，那麼查詢將被寫為：

SELECT StudentName, State, City FROM Students ORDER BY (
CASE
WHEN State IS NULL THEN City
ELSE State
END);

從這裡我們將知道，當我們在 SELECT 查詢中應用 CASE 語句來獲取滿足特定 case 條件的特定值時。在上圖中，我們將 CASE 語句放在 State 和 City 列上，如果 State 列包含 NULL 值，則查詢顯示 City 列值，如果 case 語句無效或找不到值，則顯示部分值，即 State返回列值。在這裡，我們使用 ORDER BY 子句獲取了應用 CASE 的學生、州和城市的名稱，以對結果行進行排序。

在 Simple CASE 中，CASE 的列值與 WHEN 子句中的條件語句值匹配以實現等價性，然後在語法中的一個之後生成結果值。但如果沒有相等的值，則返回 ELSE 部分值（如果提供）。

您不應在 WHEN 子句值中使用 NULL，因為如果執行，邏輯部分將為 NULL = NULL，結果為 FALSE。

現在對於 Search CASE，它遵循與 Simple CASE 相同的邏輯過程，但搜尋案例的一部分使其執行起來有所不同。在這裡，滿足 MySQL 資料型別的值的 CASE 是基於它用於的上下文。例如，如果語句中使用了字串上下文，則結果值將採用相同的資料型別，字串。此外，如果 CASE 條件表示式使用數位上下文，則返回的值將是整數、小數或實數值資料型別。

帶有 SELECT 和 ORDER BY 子句的 CASE 語句

建立資料庫表

CREATE TABLE Students(StudentID int, StudentName varchar(255), State varchar(255), City varchar(255));

往資料庫插入新值

INSERT INTO Students (StudentID, StudentName, State, City) VALUES ('01', 'Wade', 'UK', 'London');
INSERT INTO Students (StudentID, StudentName, State, City) VALUES ('02', 'Vivi', 'US', 'NewYork');
INSERT INTO Students (StudentID, StudentName, State, City) VALUES ('03', 'Kyrie', 'CN', 'SH');
INSERT INTO Students (StudentID, StudentName, State, City) VALUES ('04', 'James', '', 'Cleveland ');

查詢資料

SELECT * FROM Students;

帶有 SELECT 查詢的 CASE 語句

SELECT StudentName, State, City FROM Students ORDER BY (
CASE
WHEN State IS NULL THEN City
ELSE State
END);

具有聚合函數的 CASE 語句

我們將使用 CASE 語句和 SUM() MySQL 函數按訂單狀態從 Orders 表中計算總銷售額。

建立資料庫表

CREATE TABLE Orders (OrderID int,SalesID int, OrderStatus varchar(255) );

插入資料

INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('10', '001', 'Shipped');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('11', '002', 'Shipped');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('12', '003', 'Not Developed');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('13', '004', 'Success');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('14', '005', 'In Process');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('15', '006', 'Cancelled');

SELECT 語句顯示錶資料

SELECT * FROM Orders;

得到的資料如下：

10   1   Shipped
11   2   Shipped
12   3   Not Developed
13   4   Success
14   5   In Process
15   6   Cancelled

在 SQL SELECT 查詢中使用 SUM() 和 COUNT 作為聚合函數的 CASE 語句

SELECT
SUM(CASE
WHEN OrderStatus = 'Success' THEN 1
ELSE 0
END) AS 'Success Count',
SUM(CASE
WHEN OrderStatus = 'On Hold' THEN 1
ELSE 0
END) AS 'Hold Count',
SUM(CASE
WHEN OrderStatus = 'In Process' THEN 1
ELSE 0
END) AS 'Processing',
SUM(CASE
WHEN OrderStatus = 'Shipped' THEN 1
ELSE 0
END) AS 'Shipping count',
SUM(CASE
WHEN OrderStatus = 'Cancelled' THEN 1
ELSE 0
END) AS 'Cancellation Count',
SUM(CASE
WHEN OrderStatus = 'Not Developed' THEN 1
ELSE 0
END) AS 'Not Developed count',
COUNT(*) AS 'Sum Total'
FROM
Orders;

執行結果：

補充：case行轉列用法

現有表資料如下圖所示（性別列中，1表示男，2表示女）：

需統計各年級男女人數。雖然表中資料已有各年級的男女人數，但是還是不夠一目瞭然。接下來使用case語句查詢如下：

SELECT
    class,
    sum( CASE WHEN sex = '1' THEN population ELSE 0 END ) AS cnt_m,
    sum( CASE WHEN sex = '2' THEN population ELSE 0 END ) AS cnt_f 
FROM
    `school_test` 
GROUP BY
    class;

結果如下：