SQL行列转换
常见的行列转换包括以下四种情况:
1.列转行
2.行转列
3.列转换成字符串
4.字符串转换成列
1.列转行
导入数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | DROP TABLE IF EXISTS `t_student`; CREATE TABLE `t_student` ( `id` int (20) NOT NULL AUTO_INCREMENT COMMENT '主键 id' , `name` varchar(50) DEFAULT NULL COMMENT '姓名' , `course` varchar(50) DEFAULT NULL COMMENT '课程' , `score` int (3) DEFAULT NULL COMMENT '成绩' , PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; INSERT INTO `t_student` VALUES (1, '张三' , '语文' , 95); INSERT INTO `t_student` VALUES (2, '李四' , '语文' , 99); INSERT INTO `t_student` VALUES (3, '王五' , '语文' , 80); INSERT INTO `t_student` VALUES (4, '张三' , '数学' , 86); INSERT INTO `t_student` VALUES (5, '李四' , '数学' , 96); INSERT INTO `t_student` VALUES (6, '王五' , '数学' , 81); INSERT INTO `t_student` VALUES (7, '张三' , '英语' , 78); INSERT INTO `t_student` VALUES (8, '李四' , '英语' , 88); INSERT INTO `t_student` VALUES (9, '王五' , '英语' , 87); INSERT INTO `t_student` VALUES (10, '张三' , '历史' , 98); INSERT INTO `t_student` VALUES (11, '李四' , '历史' , 85); INSERT INTO `t_student` VALUES (12, '王五' , '历史' , 89); |
t_student表 (学生成绩表)
1.1 MAX(CASE WEHN)方法
1 2 3 4 5 6 7 | SELECT name as '姓名' , MAX(CASE WHEN course = '语文' THEN score END) AS '语文' , MAX(CASE WHEN course = '数学' THEN score END) AS '数学' , MAX(CASE WHEN course = '英语' THEN score END) AS '英语' , MAX(CASE WHEN course = '历史' THEN score END) AS '历史' FROM t_student GROUP BY name; |
结果展示:
涉及知识点: CASE表达式 | 聚合函数
1.2 SUM(IF(条件,列值,0))
1 2 3 4 5 6 7 | SELECT name as '姓名' , SUM(IF(course = '语文' ,score,0)) AS '语文' , SUM(IF(course = '数学' ,score,0)) AS '数学' , SUM(IF(course = '英语' ,score,0)) AS '英语' , SUM(IF(course = '历史' ,score,0)) AS '历史' FROM t_student GROUP BY name; |
结果展示:
涉及知识点: IF函数
2.行转列
导入数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DROP TABLE IF EXISTS `t_course`; CREATE TABLE `t_course` ( `id` int (11) NOT NULL AUTO_INCREMENT, `user_name` varchar(50) DEFAULT NULL COMMENT '用户名' , `chinese` double DEFAULT NULL COMMENT '语文成绩' , `math` double DEFAULT NULL COMMENT '数学成绩' , `english` double DEFAULT NULL COMMENT '英语成绩' , `history` double DEFAULT NULL COMMENT '历史成绩' , PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; INSERT INTO t_course VALUES ( '1' , '张三' , '95' , '86' , '78' , '98' ); INSERT INTO t_course VALUES ( '2' , '李四' , '99' , '96' , '88' , '85' ); INSERT INTO t_course VALUES ( '3' , '王五' , '80' , '81' , '87' , '89' ); |
t_course表
行转列的过程, 其实就是列转行的逆过程
1 2 3 4 5 6 7 8 9 | -- 列转行:通过UNION或UNION ALL实现 SELECT user_name, '语文' AS course,chinese AS score FROM t_course UNION ALL SELECT user_name, '数学' AS course,math AS score FROM t_course UNION ALL SELECT user_name, '英语' AS course,english AS score FROM t_course UNION ALL SELECT user_name, '政治' AS course,history AS score FROM t_course ORDER BY user_name; |
部分结果展示:
涉及知识点: 组合查询
UNION 与 UNION ALL的区别:
1.对重复结果的处理: UNION会去掉重复记录,UNION ALL不会
2.对排序的处理: UNION会排序,UNION ALL只是简单地将两个结果集合并
3.效率方面的区别: 因为UNION会做去重和排序处理,因此效率比UNION ALL慢很多
3.列转换成字符串
在某些场景下,我们可能会对单列或者多列转换成字符串,实现这个需求需要使用到 GROUP_CONCAT函数
语法格式
GROUP_CONCAT([DISTINCT] 要连接的字段 [ORDER BY 排序字段 ASC/DESC] [SEPARATOR '分隔符'])
导入数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | DROP TABLE IF EXISTS `t_student`; CREATE TABLE `t_student` ( `id` int (20) NOT NULL AUTO_INCREMENT COMMENT '主键 id' , `name` varchar(50) DEFAULT NULL COMMENT '姓名' , `course` varchar(50) DEFAULT NULL COMMENT '课程' , `score` int (3) DEFAULT NULL COMMENT '成绩' , PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; INSERT INTO `t_student` VALUES (1, '张三' , '语文' , 95); INSERT INTO `t_student` VALUES (2, '李四' , '语文' , 99); INSERT INTO `t_student` VALUES (3, '王五' , '语文' , 80); INSERT INTO `t_student` VALUES (4, '张三' , '数学' , 86); INSERT INTO `t_student` VALUES (5, '李四' , '数学' , 96); INSERT INTO `t_student` VALUES (6, '王五' , '数学' , 81); INSERT INTO `t_student` VALUES (7, '张三' , '英语' , 78); INSERT INTO `t_student` VALUES (8, '李四' , '英语' , 88); INSERT INTO `t_student` VALUES (9, '王五' , '英语' , 87); INSERT INTO `t_student` VALUES (10, '张三' , '历史' , 98); INSERT INTO `t_student` VALUES (11, '李四' , '历史' , 85); INSERT INTO `t_student` VALUES (12, '王五' , '历史' , 89); |
t_student表 (学生成绩表)
问题: 实现t_student表中课程和成绩拼接为一个字符串的功能
1 2 3 | SELECT name, GROUP_CONCAT(course, ":" , score) AS '课程:成绩' FROM t_student GROUP BY name; |
结果展示:
涉及知识点: GROUP_CONCAT函数
4.字符串转换成列
在某些场景下,我们需要把某一列的字符串转成多列
导入数据
t_user_order表 (用户订单表)
1 2 3 4 5 6 7 8 9 10 11 | DROP TABLE IF EXISTS `t_user_order`; CREATE TABLE `t_user_order` ( `id` int (11) NOT NULL AUTO_INCREMENT COMMENT '主键 id' , `user_id` varchar(50) DEFAULT NULL COMMENT '用户 id' , `order_id` varchar(100) DEFAULT NULL COMMENT '订单 ids' , PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; INSERT INTO t_user_order VALUES ( '1' , 'user1' , '1,3,5,19,20' ); INSERT INTO t_user_order VALUES ( '2' , 'user2' , '2,4,6,8,30,50' ); INSERT INTO t_user_order VALUES ( '3' , 'user3' , '11,15,29,31,33' ); |
结果展示:
从上表可以看出用户ID (user_id)和订单ID (order_id)之间的关系是一对多关系,用户ID对应的订单 ID是一个字符串
问题: 将order_id中的字符串转换成列
思路: 利用help_topic表把以逗号分隔的字符串转换成行
1 2 3 4 5 6 | -- 字符串转换成列: 利用SUBSTRING_INDEX和mysql.help_topic实现 SELECT a.user_id, SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_id, ',' , b.help_topic_id + 1 ), ',' ,- 1 )AS order_id FROM t_user_order AS a LEFT JOIN mysql.help_topic AS b ON b.help_topic_id < (length(a.order_id) - length(REPLACE(a.order_id, ',' , '' )) + 1); |
部分结果展示:
————————————————
版权声明:本文为CSDN博主「山茶花开时。」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/Hudas/article/details/124055641
本文来自博客园,作者:王晓升,转载请注明原文链接:https://www.cnblogs.com/xiaosheng1989/p/16457804.html
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战