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 |
+---------------+--------+--------------+--------+---------+--------------+--------+--------+------+--------+
