mysql怎么把行变列(MySQL 中行转列的方法)
mysql怎么把行变列
MySQL 中行转列的方法MySQL行转列操作
所谓的行转列操作,就是将一个表的行信息转化为列信息,说着可能比较笼统,这里先举个例子,如下:
|
+ ----+-----------+--------+-------+ | ID | USER_NAME | COURSE | SCORE | + ----+-----------+--------+-------+ | | 张三 | 数学 | | | | 张三 | 语文 | | | | 张三 | 英语 | | | | 李四 | 数学 | | | | 李四 | 语文 | | | | 李四 | 英语 | | | | 王五 | 数学 | | | | 王五 | 语文 | | | | 王五 | 英语 | | + ----+-----------+--------+-------+ rows in set (0.00 sec) + -----------+--------+--------+--------+ | user_name | 数学 | 语文 | 英语 | + -----------+--------+--------+--------+ | 张三 | | | | | 李四 | | | | | 王五 | | | | + -----------+--------+--------+--------+ rows in set (0.00 sec) |
上面的例子中,表1给出了三个学生的三门成绩,而表2是将表1的行记录信息(学科、姓名)转化为列信息,并根据不同的user_name进行分组显示。
1 case when操作方法
要实现上面的功能,我们需要进行分析,首先,我们需要生成三个列,分别是数学,语文和英语,然后给每个列中的值填入对应的数据。这里需要用到mysql的case when then end操作,也就是条件操作,关于这个条件语句,首先我们给出解释:
|
case colume when condition1 then result1 when condition2 then result2 when condition3 then result3 else result4 end |
上面的语法,可以理解为当column的值符合condition1的时候,用result1去替换column的值,以此类推,当column值都不符合的时候,用result4去替换column的值。
现在开始试验:
首先我们创建一张表,并插入如下数据:
|
mysql-yeyz ::>> select * from test_tbl; + ----+-----------+--------+-------+ | ID | USER_NAME | COURSE | SCORE | + ----+-----------+--------+-------+ | | 张三 | 数学 | | | | 张三 | 语文 | | | | 张三 | 英语 | | | | 李四 | 数学 | | | | 李四 | 语文 | | | | 李四 | 英语 | | | | 王五 | 数学 | | | | 王五 | 语文 | | | | 王五 | 英语 | | + ----+-----------+--------+-------+ rows in set (0.00 sec) |
根据上面case when语法,当碰到课程为'数学'的时候,我们定义一个列'数学',并把它的score填入其中,如果碰到'语文'或者'英语',那么把它替换为0,我们可以先笼统的写出如下SQL:
|
mysql-yeyz ::>> SELECT user_name , ( CASE course WHEN '数学' THEN score ELSE END ) 数学 FROM test_tbl; + -----------+--------+ | user_name | 数学 | + -----------+--------+ | 张三 | | | 张三 | | | 张三 | | | 李四 | | | 李四 | | | 李四 | | | 王五 | | | 王五 | | | 王五 | | + -----------+--------+ rows in set (0.00 sec) |
我们发现上面的表只有两个列,根据SQL规则,我们可以把'语文'和'英语'的值也加入进去,一次性多写几个列,如下:
|
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 | 数学 | 语文 | 英语 | + -----------+--------+--------+--------+ | 张三 | | | | | 李四 | | | | | 王五 | | | | + -----------+--------+--------+--------+ |
好像就剩把那些同名的学生信息都合并一下就好了,自然而然我们想到了group_by(user_name)的操作,而group_by操作需要和一些聚合函数(MAX,MIN,AVG,SUM,COUNT等)进行搭配。由于每条记录中只包含当前学科的成绩,其他学科的成绩为0,所以我们使用MAX函数和SUM函数的结果是相同的,但是不能使用AVG函数和MIN函数,这应该很好理解吧。
下面我们给出最终结果:
|
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 行转列的资料请关注开心学习网其它相关文章!
原文链接:https://cloud.tencent.com/developer/article/1533688
- mysql全套优化(Mysql优化神器推荐)
- 对mysql索引的理解(详解MySQL 8.0 之不可见索引)
- key的用法归纳mysql(详解mysql中explain的type)
- php不能用mysql(php写入mysql中文乱码的实例解决方法)
- mysql left join索引怎么使用(详解mysql 使用left join添加where条件的问题分析)
- mysql字段多有什么问题(MySQL编码不一致可能引起的一些问题)
- apache2.4支持php5.5吗(WINDOWS下php5.2.4+mysql6.0+apache2.2.4+ZendOptimizer-3.3.0配置)
- binlog怎么恢复mysql数据库(mysql5.7使用binlog 恢复数据的方法)
- mysql5.5.36版本介绍(WDCP控制面板升级mysql为5.7.11的方法)
- mysql数据库简单操作(一篇文章教会你进行MySQL数据库和数据表的基本操作)
- mysqlgroupby语句实现原理(Mysql中错误使用SQL语句Groupby被兼容的情况)
- mysql substr函数的用法(MySQL切分函数substring的具体使用)
- mysql用什么类型数据(MySQL 数据类型选择原则)
- mysql 索引举例(MySQL 各个索引的使用详解)
- mysql 分库分表步骤(MySQL读多写少设计方案 - 分库分表还能这么做?)
- mysql自增主键创建过程(深入谈谈MySQL中的自增主键)
- 苹果自研芯片跑分对比 A16芯片排名靠后,M1系列霸榜(苹果自研芯片跑分对比)
- X86处理器的梦魇 苹果M1自研芯片到底有多强(苹果M1自研芯片到底有多强)
- 泰剧《爱欲之神》Boom kitkong和Great合体杂志(泰剧爱欲之神Boomkitkong和Great合体杂志)
- 素人恋爱综艺火药味十足 男生为赢得芳心集体扯头花,真是出好戏(素人恋爱综艺火药味十足)
- 《囧妈》为何受抵制 春节七部影片撤档背后的责任与博弈(囧妈为何受抵制)
- 提醒 2019年起河南驾考要开设科目五 官方回应来了(2019年起河南驾考要开设科目五)
热门推荐
- php和java联系(PHP实现与java 通信的插件使用教程)
- linux下启动tomcat服务(Linux系统安装Tomcat并配置Service启动关闭)
- 关于mysql性能优化的描述(Mysql性能优化之索引下推)
- dedecms列表分页代码(Dedecms文章设置推荐后列表页标题自动加粗的解决方法)
- 多个图片拼接python实现(python实现两张图片的像素融合)
- dedecms标签语法(DEDECMS文档关键词关联文章BUG的修正方法)
- vue组件方法里如何修改data(vue项目中使用rem替换px的实现示例)
- python pandas dataframe 查询(Python实现从SQL型数据库读写dataframe型数据的方法基于pandas)
- django分页器图(Django框架实现分页显示内容的方法详解)
- MVC中报get_RazorKeywords()错误的解决方法
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9