数据库行列数据转换

说与风听

已于 2022-05-25 16:16:49 修改

阅读量1.5k

点赞数 1

分类专栏：数据库文章标签：数据库 mysql sql

于 2022-05-11 10:26:25 首次发布

本文链接：https://blog.csdn.net/xuforvic/article/details/124703975

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

数据库中行列进行相互转换

前半部分是看到一个博主的视频，链接：https://www.bilibili.com/video/BV1MF411z7X9

以MySQL为例，首先创建一个测试表。
在这里插入图片描述

#创建表
CREATE TABLE `t_score`  (
  `sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `grade` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
#插入数据
-- ----------------------------
-- Records of t_score
-- ----------------------------
INSERT INTO `t_score` VALUES ('张三', '语文', 80);
INSERT INTO `t_score` VALUES ('李四', '语文', 77);
INSERT INTO `t_score` VALUES ('王五', '语文', 91);
INSERT INTO `t_score` VALUES ('张三', '数学', 85);
INSERT INTO `t_score` VALUES ('李四', '数学', 90);
INSERT INTO `t_score` VALUES ('王五', '数学', 60);
INSERT INTO `t_score` VALUES ('张三', '英语', 81);
INSERT INTO `t_score` VALUES ('李四', '英语', 69);
INSERT INTO `t_score` VALUES ('王五', '英语', 82);

思路：我们可以查三个科目字段，然后达到这种效果，对个查询进行case when

在这里插入图片描述

#使用case when then 对每个成绩进行过滤
select sname,
			 case cname when '语文' then grade else 0 end as "语文",
			 case cname when '数学' then grade else 0 end as "数学",
			 case cname when '英语' then grade else 0 end as "英语"
from t_score

但是这样查询出来，因为每个列只有一种，所以会有0的值
在这里插入图片描述
这个时候我们对结果进行分组，然后对每个列进行求和，这样就不会出现0

select sname,
			 sum(case cname when '语文' then grade else 0 end) as "语文",
			 sum(case cname when '数学' then grade else 0 end) as "数学",
			 sum(case cname when '英语' then grade else 0 end) as "英语"
from t_score
group by sname

在这里插入图片描述

其实我们转换过去，就能想到怎么转换回来，我们写三个SQL进行拼接，每个只查询一个科目和成绩，然后使用union all 进行拼接

#先创建出模拟数据
CREATE view `t_score_s1` as 
select sname,
			 sum(case cname when '语文' then grade else 0 end) as "YW",
			 sum(case cname when '数学' then grade else 0 end) as "SX",
			 sum(case cname when '英语' then grade else 0 end) as "YY"
from t_score
group by sname

在使用SQL分别查询每个科目的成绩，使用union all 拼接
select sname,'语文' as cname,yw as grade from t_score_s1
union all 
select sname,'数学' as cname,SX as grade from t_score_s1
union all 
select sname,'英语' as cname,YY as grade from t_score_s1