MySQL 中行转列的方法
16lz
2021-12-14
MySQL行转列操作
所谓的行转列操作，就是将一个表的行信息转化为列信息，说着可能比较笼统，这里先举个例子，如下：
+----+-----------+--------+-------+| ID | USER_NAME | COURSE | SCORE |+----+-----------+--------+-------+| | 张三 | 数学 | || | 张三 | 语文 | || | 张三 | 英语 | || | 李四 | 数学 | || | 李四 | 语文 | || | 李四 | 英语 | || | 王五 | 数学 | || | 王五 | 语文 | || | 王五 | 英语 | |+----+-----------+--------+-------+ rows in set (0.00 sec)+-----------+--------+--------+--------+| user_name | 数学 | 语文 | 英语 |+-----------+--------+--------+--------+| 张三 | | | || 李四 | | | || 王五 | | | |+-----------+--------+--------+--------+ rows in set (0.00 sec)
1 case when操作方法
要实现上面的功能，我们需要进行分析，首先，我们需要生成三个列，分别是数学，语文和英语，然后给每个列中的值填入对应的数据。这里需要用到mysql的case when then end操作，也就是条件操作，关于这个条件语句，首先我们给出解释：
case colume when condition1 then result1 when condition2 then result2 when condition3 then result3else result4end
现在开始试验：
首先我们创建一张表，并插入如下数据：
mysql-yeyz ::>>select * from test_tbl;+----+-----------+--------+-------+| ID | USER_NAME | COURSE | SCORE |+----+-----------+--------+-------+| | 张三 | 数学 | || | 张三 | 语文 | || | 张三 | 英语 | || | 李四 | 数学 | || | 李四 | 语文 | || | 李四 | 英语 | || | 王五 | 数学 | || | 王五 | 语文 | || | 王五 | 英语 | |+----+-----------+--------+-------+ rows in set (0.00 sec)
mysql-yeyz ::>>SELECT user_name ,(CASE course WHEN '数学' THEN score ELSE END ) 数学 FROM test_tbl;+-----------+--------+| user_name | 数学 |+-----------+--------+| 张三 | || 张三 | || 张三 | || 李四 | || 李四 | || 李四 | || 王五 | || 王五 | || 王五 | |+-----------+--------+ rows in set (0.00 sec)
mysql-yeyz ::>>SELECT user_name , -> (CASE course WHEN '数学' THEN score ELSE END ) 数学, -> (CASE course WHEN '语文' THEN score ELSE END ) 语文, -> (CASE course WHEN '英语' THEN score ELSE END ) 英语 -> FROM test_tbl;+-----------+--------+--------+--------+| user_name | 数学 | 语文 | 英语 |+-----------+--------+--------+--------+| 张三 | | | || 张三 | | | || 张三 | | | || 李四 | | | || 李四 | | | || 李四 | | | || 王五 | | | || 王五 | | | || 王五 | | | |+-----------+--------+--------+--------+ rows in set (0.00 sec)
+-----------+--------+--------+--------+| user_name | 数学 | 语文 | 英语 |+-----------+--------+--------+--------+| 张三 | | | || 李四 | | | || 王五 | | | |+-----------+--------+--------+--------+
下面我们给出最终结果：
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)
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)
当我们把基本的行转列实现之后，我们现在需要在转换之后的表上面添加一个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)
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)
如果我们对于结果的显示格式要求不是那么细致的话，也可以用一种粗犷的方法，就是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)
更多相关文章
- ES6 变量声明，箭头函数，数组方法，解构赋值，JSON，类与继承，模块化练习
- 浅谈Java中Collections.sort对List排序的两种方法
- Python list sort方法的具体使用
- python list.sort()根据多个关键字排序的方法实现
- android上一些方法的区别和用法的注意事项
- android实现字体闪烁动画的方法
- Android中dispatchDraw分析
- Android四大基本组件介绍与生命周期
- Android(安卓)MediaPlayer 常用方法介绍