萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL CASE 語句完整指南:從基礎語法到實戰進階技巧

MySQL CASE 語句是 SQL 中實現條件邏輯的核心工具,相當於程式語言的 if-else。本文系統整理兩種語法形式、常見陷阱、日期比較技巧及實戰案例,助你靈活掌握條件表達。

一、兩種基本語法形式

1. 簡單 CASE(Simple CASE)

比較單一運算式與多個值:

CASE column_name
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

2. 搜尋 CASE(Searched CASE)

評估多個布林表達式:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

二、實戰範例演示

範例環境

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

INSERT INTO lee(name,birthday) VALUES ('sam','1990-01-01');
INSERT INTO lee(name,birthday) VALUES ('lee','1980-01-01');
INSERT INTO lee(name,birthday) VALUES ('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 label
FROM lee;

3. 混合條件:生日與姓名組合判斷

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

三、日期比較的關鍵陷阱與最佳實踐

錯誤寫法:WHEN birthday > '1988' THEN ...
字串比較會按字典序,'1990-01-01' > '1988' 為真,但 '1980-12-31' > '1988' 也為真(字串比較只看前四字元),導致邏輯錯誤。

正確寫法二選一:

  1. 完整日期字串:WHEN birthday > '1988-12-31' THEN ...
  2. 使用 YEAR() 函數(推薦,可用索引且語意清晰):
SELECT name,
    CASE
        WHEN YEAR(birthday) > 1988 THEN 'young'
        WHEN YEAR(birthday) < 1980 THEN 'old'
        ELSE 'ok'
    END AS age_group
FROM lee;

四、進階實戰:罰款分級與統計

建表與測試資料

CREATE TABLE penalties (
    paymentno INT NOT NULL,
    payment_date DATE NOT NULL,
    amount DECIMAL(7,2) NOT NULL,
    PRIMARY KEY(paymentno)
);

INSERT INTO penalties VALUES (1,'2008-01-01',3.45);
INSERT INTO penalties VALUES (2,'2009-01-01',50.45);
INSERT INTO penalties VALUES (3,'2008-07-01',80.45);

案例 1:金額分級標記

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 級罰款編號(子查詢復用 CASE 結果)

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';

五、核心應用場景:解決 LEFT JOIN 缺失行計數為 0 的問題

場景:表 A(主表)LEFT JOIN 表 B(狀態表),統計每個 master_id 下 status=6 的訂單數。若某 master_id 無符合狀態的記錄,COUNT 返回 NULL 而非 0。

錯誤寫法

SELECT a.master_id, COUNT(a.order_id)
FROM a LEFT JOIN b ON a.order_id = b.order_id
WHERE b.status = 6
GROUP BY a.master_id;

WHERE 條件將 LEFT JOIN 退化為 INNER JOIN,導致缺失行消失。

正確寫法:CASE + SUM

SELECT a.master_id,
    SUM(CASE WHEN b.status = 6 THEN 1 ELSE 0 END) AS total_count
FROM a
INNER JOIN b ON a.order_id = b.id
GROUP BY a.master_id;

原理:CASE 逐行評估,滿足條件計 1 否則 0,SUM 累加即得計數,無匹配行自然得 0。

六、總結與建議

  • CASE 可用於 SELECT、WHERE、ORDER BY、GROUP BY、HAVING 等子句
  • 優先使用 YEAR()、MONTH() 等函數進行日期比較,避免字串比較陷阱
  • 複雜邏輯建議用搜尋 CASE,可讀性更強
  • 統計條件計數時,SUM(CASE...) 比 COUNT(CASE...) 更直觀(後者需配合 NULL 處理)
  • 嵌套 CASE 影響可讀性,超過 3 層建議拆分為視圖或應用層處理
copyright © 萬盛學電腦網 all rights reserved