MySQL 單條 SQL 實現多條件統計計數：SUM(IF()) 技巧全解析

問題場景

成績單中需同時統計「及格人數 (>=60)」與「不及格人數 (<60)」，傳統寫法需兩次查詢或 UNION，能否單條 SQL 搞定？

傳統寫法 (兩次查詢 + JOIN)

SELECT a.name, a.count_plus, b.count_neg
FROM (
    SELECT name, COUNT(id) AS count_plus
    FROM score2 WHERE score >= 60 GROUP BY name
) a
JOIN (
    SELECT name, COUNT(id) AS count_neg
    FROM score2 WHERE score < 60 GROUP BY name
) b ON a.name = b.name;

缺點： 掃描表兩次、需 JOIN、代碼冗長。

單條 SQL 核心技巧：SUM(IF()) / SUM(CASE)

SELECT 
    name,
    SUM(IF(score >= 60, 1, 0)) AS pass_count,
    SUM(IF(score < 60, 1, 0)) AS fail_count
FROM score2
GROUP BY name;

原理： IF(condition, 1, 0) 將條件轉為 1/0，**SUM 求和即等於計數**。MySQL 特有語法，極其高效。

標準 SQL 寫法：SUM(CASE) (跨數據庫通用)

SELECT 
    name,
    SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) AS pass_count,
    SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS fail_count
FROM score2
GROUP BY name;

推薦： 生產環境優先用 CASE WHEN，符合 SQL 標準，Oracle/PostgreSQL/SQL Server 通用。

進階應用：多維度條件統計

SELECT 
    department,
    COUNT(*) AS total,
    SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_cnt,
    SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_cnt,
    SUM(CASE WHEN age >= 18 THEN 1 ELSE 0 END) AS adult_cnt,
    SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) AS excellent_cnt,
    SUM(CASE WHEN score >= 60 AND score < 90 THEN 1 ELSE 0 END) AS good_cnt,
    SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS poor_cnt
FROM students
GROUP BY department;

一條 SQL 完成 7 個維度統計，**單次全表掃描**，效能碾壓多查詢方案。

COUNT() 函數深度解析

實戰案例：女生成績 > 90 統計

低效寫法：

SELECT 女生成績 FROM user WHERE 成績 > 90;
-- 應用層 mysql_num_rows() 計數
-- 10萬行需傳輸 10萬行數據到客戶端，極慢

高效寫法：

SELECT COUNT(*) FROM user WHERE gender = 'F' AND score > 90;
-- 僅返回 1 行 1 列，網絡/CPU/內存開銷極小

分組統計：

SELECT gender, COUNT(*) 
FROM user 
WHERE score > 90 
GROUP BY gender;

避坑指南

1. WHERE vs HAVING： 聚合前過濾用 WHERE (利用索引)，聚合後過濾用 HAVING
2. NULL 值處理： COUNT(列) 忽略 NULL，SUM(IF(列 IS NULL, 1, 0)) 統計 NULL
3. 大數據量 COUNT(*)： InnoDB 需掃描聚簇索引，千萬級表可能秒級。可用 SHOW TABLE STATUS 估算值、觸發器維護計數表、或 EXPLAIN 估算
4. 不要用 COUNT(*) OVER() 窗口函數統計總數 (MySQL 8.0+ 支援但效能差)，應用層分頁時單獨查 COUNT(*)