MySQL CASE WHEN 多條件查詢實戰：任意 N 條件滿足 M 個的靈活寫法

場景

表 DemoTable 有欄位 condition1~5，查詢**任意滿足 2 個以上條件**的記錄。

CASE WHEN 累加法

SELECT * FROM DemoTable
WHERE (
    (SELECT CASE WHEN condition1符合條件 THEN 1 ELSE 0 END FROM DemoTable) +
    (SELECT CASE WHEN condition2符合條件 THEN 1 ELSE 0 END FROM DemoTable) +
    (SELECT CASE WHEN condition3符合條件 THEN 1 ELSE 0 END FROM DemoTable) +
    (SELECT CASE WHEN condition4符合條件 THEN 1 ELSE 0 END FROM DemoTable) +
    (SELECT CASE WHEN condition5符合條件 THEN 1 ELSE 0 END FROM DemoTable)
) >= 2;

優化寫法：單次掃描 + CASE 運算

SELECT * FROM DemoTable
WHERE (
    CASE WHEN condition1符合條件 THEN 1 ELSE 0 END +
    CASE WHEN condition2符合條件 THEN 1 ELSE 0 END +
    CASE WHEN condition3符合條件 THEN 1 ELSE 0 END +
    CASE WHEN condition4符合條件 THEN 1 ELSE 0 END +
    CASE WHEN condition5符合條件 THEN 1 ELSE 0 END
) >= 2;

優點：
- 僅掃描一次表。
- 無子查詢開銷。
- 可直接在 SELECT 列表顯示滿足條件數：
SELECT *, (CASE ... + ...) AS matched_cnt FROM DemoTable HAVING matched_cnt >= 2;

條件判斷範例

CASE WHEN status = 'active' AND score > 80 THEN 1 ELSE 0 END
CASE WHEN created_at > '2023-01-01' THEN 1 ELSE 0 END
CASE WHEN category IN ('A','B') THEN 1 ELSE 0 END

延伸：動態條件組合

若條件來自應用層參數，可在應用組裝 SQL 片段，或用 PREPARE 動態執行。