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

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

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

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和 |
+---------+---------+---------+---------+
|      12 |       2 |      10 |      12 |
+---------+---------+---------+---------+

 使用第一种方法，如果数据比较多的情况，会很复杂，不够简洁。

第二种方法：

 

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和 |
+---------+---------+---------+---------+
|      12 |       2 |      10 |      12 |
+---------+---------+---------+---------+
使用分支控制case when then else end 来实现

 

 

列转行--