数据库中行列进行相互转换
前半部分是看到一个博主的视频,链接: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