使用UNION实现mysql 行列变换（一）

表格1：表名：score

+------+---------+---------+------+-------+
| name | chinese | english | math | total |
+------+---------+---------+------+-------+
| Tom  |      90 |      80 |  100 |   270 |
+------+---------+---------+------+-------+

表格2：

+---------+-------+
| subject | value |
+---------+-------+
| 语文    |    90 |
| 数学    |   100 |
| 英语    |    80 |
| 总分    |   270 |
+---------+-------+

要把表格1 变成 表格2

方法1：

第一步：

select ''subject,0 value from dual;

结果为：

+---------+-------+
| subject | value |
+---------+-------+
|         |     0 |
+---------+-------+

第二步：

select '语文' subject, (select chinese from score) value;

结果为：

+---------+-------+
| subject | value |
+---------+-------+
| 语文    |    90 |
+---------+-------+

使用union将结果组合

select '语文' subject, (select chinese from score) value 
union 
select '数学' subject, (select math from score) value 
union 
select '英语' subject,(select english from score) value 
union 
select '总分' subject,(select total from score) value;

+---------+-------+
| subject | value |
+---------+-------+
| 语文    |    90 |
| 数学    |   100 |
| 英语    |    80 |
| 总分    |   270 |
+---------+-------+