MySQL **不支援** condition ? val1 : val2 語法。標準 SQL 提供兩種條件表達式:
表結構:price(原價)、pay(優惠價,0 表示無優惠)、num(數量)。
需求:按實際成交金額排序,pay > 0 用優惠價,否則用原價。
SELECT
CASE
WHEN pay > 0 THEN pay * num
ELSE price * num
END AS amount
FROM orders
ORDER BY amount DESC;
SELECT IF(pay > 0, pay * num, price * num) AS amount
FROM orders
ORDER BY amount DESC;
SELECT
CASE
WHEN pay > 0 THEN pay * num
WHEN discount > 0 THEN price * (1 - discount) * num
ELSE price * num
END AS final_amount
FROM orders;
-- IFNULL/COALESCE 組合
SELECT COALESCE(pay, price) * num AS amount FROM orders;
-- CASE 精確判斷 NULL
SELECT
CASE
WHEN pay IS NOT NULL AND pay > 0 THEN pay * num
ELSE price * num
END AS amount
FROM orders;
SELECT
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_total,
SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refund_total
FROM orders;
CASE WHEN 與 IF 在優化器層面無本質性能差異。WHERE / JOIN ON 中對列使用函數/表達式,導致索引失效。條件表達式建議用於 SELECT 列表或 ORDER BY。MySQL 使用 CASE WHEN ... END(標準、多條件)或 IF()(簡潔、雙分支)實現三元邏輯。選型原則:可讀性優先,複雜邏輯用 CASE,簡單雙分支用 IF。善用條件聚合實現單掃描多指標統計,提升查詢效率。