MySQL 中行转列的方法(2)-木庄网络博客

下面我们给出最终结果：

mysql-yeyz 13:55:52>>SELECT user_name ,
  ->   MAX(CASE course WHEN '数学' THEN score ELSE END ) 数学,
  ->   MAX(CASE course WHEN '语文' THEN score ELSE END ) 语文,
  ->   MAX(CASE course WHEN '英语' THEN score ELSE END ) 英语
  -> FROM test_tbl
  -> GROUP BY USER_NAME;
+-----------+--------+--------+--------+
| user_name | 数学  | 语文  | 英语  |
+-----------+--------+--------+--------+
| 张三   |   34 |   58 |   58 |
| 李四   |   45 |   87 |   45 |
| 王五   |   76 |   34 |   89 |
+-----------+--------+--------+--------+
3 rows in set (0.00 sec)



mysql-yeyz ::>>SELECT user_name ,
  ->   sum(CASE course WHEN '数学' THEN score ELSE END ) 数学,
  ->   sum(CASE course WHEN '语文' THEN score ELSE END ) 语文,
  ->   sum(CASE course WHEN '英语' THEN score ELSE END ) 英语
  -> FROM test_tbl
  -> GROUP BY USER_NAME;
+-----------+--------+--------+--------+
| user_name | 数学  | 语文  | 英语  |
+-----------+--------+--------+--------+
| 张三   |   34 |   58 |   58 |
| 李四   |   45 |   87 |   45 |
| 王五   |   76 |   34 |   89 |
+-----------+--------+--------+--------+
3 rows in set (0.00 sec)


mysql-yeyz ::>>SELECT user_name ,
  ->   MIN(CASE course WHEN '数学' THEN score ELSE END ) 数学,
  ->   MIN(CASE course WHEN '语文' THEN score ELSE END ) 语文,
  ->   MIN(CASE course WHEN '英语' THEN score ELSE END ) 英语
  -> FROM test_tbl
  -> GROUP BY USER_NAME;
+-----------+--------+--------+--------+
| user_name | 数学  | 语文  | 英语  |
+-----------+--------+--------+--------+
| 张三   |   0 |   0 |   0 |
| 李四   |   0 |   0 |   0 |
| 王五   |   0 |   0 |   0 |
+-----------+--------+--------+--------+
3 rows in set (0.00 sec)

可以看出来，使用MAX和使用SUM的结果是一样的，但是使用MIN作为聚合函数，会导致最终输出的结果都是0，因为每次都选的是该user_name指定学科的最小的值，也就是0。这样的结果就很好理解了。

2 if操作方法

上面的case when操作方法理解了，那么if的操作方法也很好理解，原理是一样的，只不过是把case when的语法转换为if方式，如下

mysql-yeyz 14:12:42>>SELECT user_name ,
  ->   MAX(if (course= '数学',score,) ) 数学,
  ->   MAX(if (course= '语文',score,) ) 语文,
  ->   MAX(if (course= '英语',score,) ) 英语
  -> FROM test_tbl
  -> GROUP BY USER_NAME;
+-----------+--------+--------+--------+
| user_name | 数学  | 语文  | 英语  |
+-----------+--------+--------+--------+
| 张三   |   34 |   58 |   58 |
| 李四   |   45 |   87 |   45 |
| 王五   |   76 |   34 |   89 |
+-----------+--------+--------+--------+
3 rows in set (0.00 sec)

3 添加total列

当我们把基本的行转列实现之后，我们现在需要在转换之后的表上面添加一个total字段，这个字段的添加我们可以通过下面的方法，即在最开始统计的时候，就把score值也统计进去，如下：

mysql-yeyz 14:18:06>>SELECT user_name ,
  ->   (CASE course WHEN '数学' THEN score ELSE END ) 数学,
  ->   (CASE course WHEN '语文' THEN score ELSE END ) 语文,
  ->   (CASE course WHEN '英语' THEN score ELSE END ) 英语,
  -> (score) total
  -> FROM test_tbl;
+-----------+--------+--------+--------+-------+
| user_name | 数学  | 语文  | 英语  | total |
+-----------+--------+--------+--------+-------+
| 张三   |   34 |   0 |   0 |  34 |
| 张三   |   0 |   58 |   0 |  58 |
| 张三   |   0 |   0 |   58 |  58 |
| 李四   |   45 |   0 |   0 |  45 |
| 李四   |   0 |   87 |   0 |  87 |
| 李四   |   0 |   0 |   45 |  45 |
| 王五   |   76 |   0 |   0 |  76 |
| 王五   |   0 |   34 |   0 |  34 |
| 王五   |   0 |   0 |   89 |  89 |
+-----------+--------+--------+--------+-------+
9 rows in set (0.00 sec)

上面的结果是没有聚合后的结果，这里需要注意的是，如果我们要聚合，前三个列可以使用sum或者max的方法，最后一个列一定要使用sum的方法，因为我们要求的是总成绩，使用max的方法会导致取值变为分数最高的那个值。最后的sql如下：

mysql-yeyz 14:18:29>>SELECT user_name ,
  ->   sum(CASE course WHEN '数学' THEN score ELSE END ) 数学,
  ->   sum(CASE course WHEN '语文' THEN score ELSE END ) 语文,
  ->   sum(CASE course WHEN '英语' THEN score ELSE END ) 英语,
  -> sum(score) total
  -> FROM test_tbl
  -> GROUP BY USER_NAME;
+-----------+--------+--------+--------+-------+
| user_name | 数学  | 语文  | 英语  | total |
+-----------+--------+--------+--------+-------+
| 张三   |   34 |   58 |   58 |  150 |
| 李四   |   45 |   87 |   45 |  177 |
| 王五   |   76 |   34 |   89 |  199 |
+-----------+--------+--------+--------+-------+
3 rows in set (0.00 sec)

4 简单方法 group_concat

如果我们对于结果的显示格式要求不是那么细致的话，也可以用一种粗犷的方法，就是group_concat函数，将所有的列都写在一起，用一个字段表示，效果如下：

mysql-yeyz 14:19:13>>SELECT user_name,
GROUP_CONCAT(`course`,":",score)AS 成绩 
FROM test_tbl 
GROUP BY user_name;
+-----------+-------------------------------+
| user_name | 成绩             |
+-----------+-------------------------------+
| 张三   | 数学:34,语文:58,英语:58    |
| 李四   | 数学:45,语文:87,英语:45    |
| 王五   | 数学:76,语文:34,英语:89    |
+-----------+-------------------------------+
3 rows in set (0.00 sec)

这种方法相当于直接针对原始表做了一个分组，也能够应付一定的应用场景。

以上就是MySQL 中行转列的方法的详细内容，更多关于MySQL 行转列的资料请关注其它相关文章！

更多相关Mysql内容来自木庄网络博客

标签：Mysql

返回前面的内容