MySQL CASE WHEN 多場景實戰詳解

MySQL CASE WHEN 多場景實戰詳解

CASE WHEN 是 MySQL 中實現條件邏輯、數據轉換、動態分組的核心函數，支援簡單/搜尋/嵌套三種形式。本文通過建表、插數、多維案例全面講解。

一、準備測試數據

CREATE TABLE lee (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name CHAR(20),
  birthday DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO lee(name,birthday) VALUES
('sam','1990-01-01'), ('lee','1980-01-01'), ('john','1985-01-01');

二、三種基本語法

1. 搜尋型 CASE（最常用）

SELECT name,
  CASE
    WHEN birthday < '1981' THEN 'old'
    WHEN birthday > '1988' THEN 'young'
    ELSE 'ok'
  END AS age_group
FROM lee;

結果：lee→old, john→ok, sam→young

2. 簡單型 CASE（等值匹配）

SELECT name,
  CASE name
    WHEN 'sam'   THEN 'young'
    WHEN 'lee'   THEN 'handsome'
    ELSE 'good'
  END AS tag
FROM lee;

3. 混合條件（同一 CASE 內混用欄位）

SELECT name, birthday,
  CASE
    WHEN birthday > '1983' THEN 'young'
    WHEN name = 'lee'      THEN 'handsome'
    ELSE 'just so so'
  END AS remark
FROM lee;

三、進階應用場景

1. 數值區間分桶（罰款金額分級）

CREATE TABLE penalties (
  paymentno INT PRIMARY KEY,
  payment_date DATE,
  amount DECIMAL(7,2)
);
INSERT INTO penalties VALUES
(1,'2008-01-01',3.45), (2,'2009-01-01',50.45), (3,'2008-07-01',80.45);

SELECT payment_date, amount,
  CASE
    WHEN amount >= 0 AND amount < 40  THEN 'low'
    WHEN amount >= 40 AND amount < 80 THEN 'moderate'
    WHEN amount >= 80                 THEN 'high'
    ELSE 'null'
  END AS level
FROM penalties;

2. 子查詢二次過濾（統計 low 級罰單編號）

SELECT * FROM (
  SELECT paymentno, amount,
    CASE
      WHEN amount >= 0 AND amount < 40  THEN 'low'
      WHEN amount >= 40 AND amount < 80 THEN 'moderate'
      WHEN amount >= 80                 THEN 'high'
      ELSE 'incorrect'
    END AS lvl
  FROM penalties
) AS p WHERE p.lvl = 'low';

3. 多欄位條件組合（設備使用狀態）

SELECT DISTINCT a.PatientID, a.PatientName,
  CASE
    WHEN b.usedstartTime IS NOT NULL AND b.UsedEndTime IS NULL THEN '1'  -- 使用中
    WHEN b.usedstartTime IS NOT NULL AND b.UsedEndTime IS NOT NULL THEN '2' -- 已歸還
  END AS usedState
FROM mets_v_patient_baseinfo a
LEFT JOIN mets_devices_used_history b ON a.PatientID = b.PatientID
WHERE (SELECT IFNULL(IsDeleted,0) FROM userpublic_info WHERE UserID = a.PatientID) = 0
ORDER BY PatientID DESC LIMIT 15;

四、常見陷阱與技巧

• 日期比較：字串比較 birthday > '1988' 視字典序，建議用 YEAR(birthday) > 1988。
• ELSE 子句：省略時預設返回 NULL，建議顯式寫 ELSE 'default' 避免應用層誤判。
• 嵌套 CASE：可嵌套多層，但超過 3 層建議改用應用層邏輯或存儲過程。
• 聚合搭配：SUM(CASE WHEN sex='M' THEN 1 ELSE 0 END) AS male_cnt 實現條件計數。

五、MySQL 8.0 新增 CASE WHEN 優化

MySQL 8.0 優化器對 CASE WHEN 常量折疊、索引下推有更好支援，複雜條件下執行計劃更穩定。