MYSQL行列转置方式！
MYSQL行列转置方式！
本文介绍了如何使用MySQL和Navicat进行列转行操作,首先,创建了一个名为`grade`的表,并插入多条数据,然后,通过修改查询SQL语句,使用`CASE`和`IF`函数将列转换为行,总结指出,`SUM`可以替换为`MAX`、`MIN`、`AVG`等聚合函数,并且在查询中需要对普通字段进行分组。
MYSQL行列转置
环境准备：mysql，navicat
本文讲解列转行：
数据库是这样显示的
想要转换成下面这样显示
开始列转行之前的准备
准备1：创建表学生分数表grade
|
1
2
3
4
5
6
|
CREATE TABLE grade(stuName VARCHAR(20) NOT NULL COMMENT '学生姓名',stuCourse VARCHAR(20) NOT NULL COMMENT '学生课程名',stuScore INT NOT NULL COMMENT '学生课程分数') |
grade表创建好了，也可以直接navicat数据库右键鼠标建表
鼠标右键单击grade设计表查看表结构设置，结果与sql语句设置一致继续往下走
准备2：往分数表grade插入多条数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
INSERT INTO grade(stuName,stuCourse,stuScore)VALUES('张三','语文',110),('张三','数学',130),('张三','英语',60),('张三','体育',40),('李四','语文',108),('李四','数学',145),('李四','英语',28),('李四','体育',48),('王五','语文',120),('王五','数学',99),('王五','英语',140),('王五','体育',42) |
鼠标双击grade查看数据是否已插入，OK
下面开始步入正题
步骤一：查询表内所有数据（注释：'*'代表所有）
|
1
|
SELECT * FROM grade |
查询结果显然不符合我们的预期
步骤二修改查询的sql语句
- 方式一：CASE
|
1
2
3
4
5
6
|
CASE case_valueWHEN when_value THENstatement_listELSEstatement_listEND CASE; |
具体代码
|
1
2
3
4
5
6
7
8
|
SELECT stuName,SUM(CASE WHEN stuCourse='语文' THEN stuScore ELSE 0 END) AS '语文',SUM(CASE WHEN stuCourse='数学' THEN stuScore ELSE 0 END) AS '数学',SUM(CASE WHEN stuCourse='英语' THEN stuScore ELSE 0 END) AS '英语',SUM(CASE WHEN stuCourse='体育' THEN stuScore ELSE 0 END) AS '体育',SUM(stuScore) AS '学科总分'FROM gradeGROUP BY stuName |
以下为代码注释：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
//SELECT 后跟查询结果SELECT stuName, //SUM 聚合函数（官方写好求和用的） // CASE WHEN stuCourse='语文' THEN stuScore ELSE 0 END用于替换值 若stuCourse='语文' 则对应显示stuScore，否则显示0//AS 重命名 列/（也叫字段）名SUM(CASE WHEN stuCourse='语文' THEN stuScore ELSE 0 END) AS '语文',SUM(CASE WHEN stuCourse='数学' THEN stuScore ELSE 0 END) AS '数学',SUM(CASE WHEN stuCourse='英语' THEN stuScore ELSE 0 END) AS '英语',SUM(CASE WHEN stuCourse='体育' THEN stuScore ELSE 0 END) AS '体育',//SUM(stuScore)求各个学科总分数SUM(stuScore) AS '学科总分'//from 后跟要查询的表名FROM grade//GROUP BY 对stuName进行分组，没有这句，会报错，要不然，就是显示第一行GROUP BY stuName |
查询结果，符合预期，列转行成功
- 方式二：if
|
1
|
IF(expr1,expr2,expr3) |
具体代码
|
1
2
3
4
5
6
7
8
|
SELECT stuName,SUM(IF(stuCourse='语文',stuScore,0)) AS '语文',SUM(IF(stuCourse='数学',stuScore,0)) AS '数学',SUM(IF(stuCourse='英语',stuScore,0)) AS '英语',SUM(IF(stuCourse='体育',stuScore,0)) AS '体育',SUM(stuScore) AS '学科总分'FROM gradeGROUP BY stuName |
效果和方式一是一样的
小节：
1.这里面的SUM可以换成MAX,MIN,AVG等等，因为各个字段如stuCourse对应一个stuScore所以用哪个都一样
2.当普通字段如stuName与聚合函数SUM（stuScore）一起查询时 stuName,SUM(字段)
必须要分组，GROUP BY stuName
不然会报错！！！
总结
以上为个人经验，希望能给大家一个参考。
学习资料见知识星球。
以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。
快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！
更多技巧， www.excelbook.cn
欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；
你将获得：
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群，共享资源。
5、优惠的会员商品。
6、一次付费只需129元，即可下载本站文章涉及的文件和软件。