業務場景

維修紀錄表 repair_record，需依 **機型** 統計各 **故障類型** 筆數，呈現「一行一機型、多欄位故障計數」的報表格式。

表結構

DESC repair_record;
+------------------------+---------------+------+-----+-------------------+-----------------------------+
| Field                  | Type          | Null | Key | Default           | Extra                       |
+------------------------+---------------+------+-----+-------------------+-----------------------------+
| id                     | bigint(20)    | NO   | PRI | NULL              | auto_increment              |
| sn                     | varchar(255)  | NO   |     | NULL              |                             |
| rack_id                | varchar(255)  | NO   |     | NULL              |                             |
| machine_model          | varchar(255)  | NO   |     | NULL              |                             |
| fault_type             | varchar(255)  | YES  |     | NULL              |                             |
| start_time             | varchar(25)   | YES  |     | NULL              |                             |
| manufacturer_arivetime | varchar(25)   | YES  |     | NULL              |                             |
| roc_donetime           | varchar(25)   | YES  |     | NULL              |                             |
| update_time            | timestamp     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| fault_desc             | varchar(2000) | YES  |     | NULL              |                             |
+------------------------+---------------+------+-----+-------------------+-----------------------------+

樣本資料

SELECT id, machine_model, fault_type, start_time FROM repair_record LIMIT 10;
+----+---------------+------------+---------------------+
| id | machine_model | fault_type | start_time          |
+----+---------------+------------+---------------------+
|  2 | Lenovo RD530  | disk       | 2015-12-20 11:40:05 |
|  4 | Lenovo RD540  | disk       | 2015-12-20 21:40:07 |
|  6 | Dell R720     | disk       | 2015-12-20 23:00:07 |
|  8 | IBM X3630 M3  | disk       | 2015-12-21 10:20:06 |
| 10 | IBM X3650 M3  | disk       | 2015-12-21 15:40:04 |
| 12 | Dell R730xd   | disk       | 2015-12-21 15:40:05 |
| 14 | IBM X3650 M3  | disk       | 2015-12-21 17:20:02 |
| 16 | Lenovo RD540  | disk       | 2015-12-21 17:40:03 |
| 18 | Dell R720xd   | disk       | 2015-12-22 10:40:09 |
| 20 | Dell R720     | disk       | 2015-12-22 11:00:06 |
+----+---------------+------------+---------------------+

核心 SQL：CASE WHEN 條件聚合

SELECT
    machine_model,
    COUNT(id) AS 總數,
    SUM(CASE fault_type WHEN 'disk'      THEN 1 ELSE 0 END) AS 硬盤故障,
    SUM(CASE fault_type WHEN 'mainboard' THEN 1 ELSE 0 END) AS 主板,
    SUM(CASE fault_type WHEN 'raidcard'  THEN 1 ELSE 0 END) AS Raid卡,
    SUM(CASE fault_type WHEN 'backboard' THEN 1 ELSE 0 END) AS 硬盤背板,
    SUM(CASE fault_type WHEN 'mem'       THEN 1 ELSE 0 END) AS 內存,
    SUM(CASE fault_type WHEN 'power'     THEN 1 ELSE 0 END) AS 電源,
    SUM(CASE fault_type WHEN 'cpu'       THEN 1 ELSE 0 END) AS CPU,
    SUM(CASE fault_type WHEN 'ilocard'   THEN 1 ELSE 0 END) AS ilo卡
FROM repair_record
GROUP BY machine_model
ORDER BY 總數 DESC;

查詢結果

+---------------+--------+--------------+--------+---------+--------------+--------+--------+------+--------+
| machine_model | 總數   | 硬盤故障     | 主板   | Raid卡  | 硬盤背板     | 內存   | 電源   | CPU  | ilo卡  |
+---------------+--------+--------------+--------+---------+--------------+--------+--------+------+--------+
| Dell R730xd   |     61 |           58 |      1 |       0 |            0 |      1 |      0 |    0 |      0 |
| Lenovo RD530  |     43 |           28 |      1 |       0 |            0 |      1 |     13 |    0 |      0 |
| IBM X3650 M3  |     39 |           39 |      0 |       0 |            0 |      0 |      0 |    0 |      0 |
| IBM X3550 M3  |     33 |           30 |      1 |       0 |            0 |      1 |      0 |    0 |      1 |
| Dell R720     |     24 |           15 |      6 |       0 |            0 |      2 |      1 |    0 |      0 |
| IBM X3630 M3  |     22 |           18 |      3 |       0 |            0 |      1 |      0 |    0 |      0 |
| Dell R720xd   |     20 |            7 |      5 |       3 |            3 |      0 |      0 |    2 |      0 |
| Lenovo RD540  |      8 |            5 |      0 |       0 |            0 |      2 |      1 |    0 |      0 |
| Lenovo RD350X |      5 |            5 |      0 |       0 |            0 |      0 |      0 |    0 |      0 |
| Lenovo RD550  |      3 |            2 |      0 |       0 |            0 |      0 |      0 |    0 |      1 |
| Dell T630     |      2 |            2 |      0 |       0 |            0 |      0 |      0 |    0 |      0 |
| Lenovo RD330  |      1 |            1 |      0 |       0 |            0 |      0 |      0 |    0 |      0 |
| Dell R630     |      1 |            1 |      0 |       0 |            0 |      0 |      0 |    0 |      0 |
| Dell R730     |      1 |            0 |      0 |       0 |            1 |      0 |      0 |    0 |      0 |
+---------------+--------+--------------+--------+---------+--------------+--------+--------+------+--------+

進階變體

1. 加入時間範圍過濾

WHERE start_time >= '2016-01-01' AND start_time < '2016-02-01'

2. 動態欄位（Prepared Statement）

SET @sql = CONCAT(
  'SELECT machine_model, COUNT(*) 總數, ',
  GROUP_CONCAT(DISTINCT CONCAT(
    'SUM(CASE fault_type WHEN ''', fault_type, ''' THEN 1 ELSE 0 END) AS `', fault_type, '`'
  )),
  ' FROM repair_record GROUP BY machine_model ORDER BY 總數 DESC');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

3. 使用 COUNT(CASE ...) 替代 SUM（語意更清晰）

COUNT(CASE WHEN fault_type = 'disk' THEN 1 END) AS 硬盤故障
-- COUNT 只計算非 NULL，CASE 不滿足條件回傳 NULL，效果等同 SUM(CASE ... THEN 1 ELSE 0 END)

效能建議

• 在 fault_type、machine_model 建立複合索引 INDEX idx_model_fault (machine_model, fault_type)，實現 **Index Only Scan** 覆蓋查詢。
• 資料量 > 億級時，考慮預先聚合物化視圖或 ClickHouse/Apache Doris 等 OLAP 引擎。