MySQL CASE 語句是 SQL 中實現條件邏輯的核心工具,相當於程式語言的 if-else。本文系統整理兩種語法形式、常見陷阱、日期比較技巧及實戰案例,助你靈活掌握條件表達。
比較單一運算式與多個值:
CASE column_name
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE default_result
END
評估多個布林表達式:
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');
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
SELECT name,
CASE name
WHEN 'sam' THEN 'young'
WHEN 'lee' THEN 'handsome'
ELSE 'good'
END AS label
FROM lee;
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' 也為真(字串比較只看前四字元),導致邏輯錯誤。
正確寫法二選一:
WHEN birthday > '1988-12-31' THEN ...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);
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;
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';
場景:表 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,導致缺失行消失。
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() 等函數進行日期比較,避免字串比較陷阱SUM(CASE...) 比 COUNT(CASE...) 更直觀(後者需配合 NULL 處理)