MYSQL开发技巧之行转列和列转行

行转列--两种方法
第一种方法：
行转列我们通常是使用交叉连接和子查询的方式做到，比如下面的例子，查询每个name的对应id的和
mysql> select * from user;
+----+------+
| id | name |
+----+------+
| | a |
| | b |
| | c |
| | d |
| | a |
| | a |
| | c |
| | d |
+----+------+

我们用分组查询能得到每个name的id的和，但是是行的形式展示的。
mysql> select name,sum(id) as 'id和' from user group by name;
+------+------+
| name | id和 |
+------+------+
| a | |
| b | |
| c | |
| d | |
+------+------+
我们可以使用别名的方式得到每一个分组的列表示，但是怎么把其他的拼接在一起呢？

mysql> select sum(id) as 'a的id和' from user where name = 'a' group by name;
+---------+
| a的id和 |
+---------+
| 12 |

通过笛卡儿积连接我们可以把每个分组连接起来，得到了行转列的结果。
mysql> select * from (select sum(id) as 'a的id和' from user where name = 'a' group by name) as a cross join (select s
m(id) as 'c的id和' from user where name = 'b' group by name)
-> as b cross join (select sum(id) as 'c的id和' from user where name = 'c' group by name) as c cross join (select
um(id) as 'd的id和' from user where name = 'd' group by name) as d;
+---------+---------+---------+---------+
| a的id和 | c的id和 | c的id和 | d的id和 |
+---------+---------+---------+---------+
| | | | |
+---------+---------+---------+---------+

mysql> select sum(case when name = 'a' then id end ) as 'a的id和',sum(case when name = 'b' then id end) as 'b的id和', su
m(case when name = 'c' then id end ) as 'c的id和', sum(case when name = 'd' then id end) as 'd的id和' from user;
+---------+---------+---------+---------+
| a的id和 | b的id和 | c的id和 | d的id和 |
+---------+---------+---------+---------+
| | | | |
+---------+---------+---------+---------+
使用分支控制case when then else end 来实现