mysql行轉列統計查詢的例子

本文章為各位介紹的是關於mysql行轉列統計查詢的一些詳細介紹,希望文章可以讓各位更好的了解mysql行轉列統計查詢的一些細節.

我們在進行統計查詢時，有時候需要將同一日期/位置等條件的不同信息進行行轉列的統計，這時候會需要用到以下的方法進行統計，相當方便。
1. 表結構
> 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              |                             |
+------------------------+---------------+------+-----+-------------------+-----------------------------+
2. 表記錄
> 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 |
+----+---------------+------------+---------------------+
3. 列轉行統計數據
SQL命令

>select machine_model, count(id) 總數, sum(case fault_type when 'disk' then 1 else 0 end ) 硬盤故障, sum(case fault_type when 'mainboard' then 1 else 0 end ) 主板, sum(case fault_type when 'raidcard' then 1 else 0 end ) Raid卡, sum(case fault_type when 'backboard' then 1 else 0 end ) 硬盤背板, sum(case fault_type when 'mem' then 1 else 0 end ) 內存, sum(case fault_type when 'power' then 1 else 0 end ) 電源, sum(case fault_type when 'cpu' then 1 else 0 end ) CPU, sum(case fault_type when 'ilocard' then 1 else 0 end ) ilo卡 from repair_record group by machine_model order by count(id) 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 |
+---------------+--------+--------------+--------+---------+--------------+--------+--------+------+--------+