mysql行转列列转行在线求助!!!!!!!!!!
mysql 行转列、列转行求助!!!!!
现在需求是将表t1转化为表t2,要求仅查询一次t1表。 附1是表ddl语句,附2是使用union all写的SQL,由于多次查询表t1,所以当t1中指标很多(例如h_score、w_score、x_score....)的情况下不适用,寻求最优解。
表t1如图所示:
表t2
-------------------------------------------------------------------------------------------------------------------------
附1
————————————————
版权声明:本文为CSDN博主「柯南爱上指针」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_35468937/article/details/105496867
#mysql##MySQL#
现在需求是将表t1转化为表t2,要求仅查询一次t1表。 附1是表ddl语句,附2是使用union all写的SQL,由于多次查询表t1,所以当t1中指标很多(例如h_score、w_score、x_score....)的情况下不适用,寻求最优解。
表t1如图所示:
表t1
表t2
-------------------------------------------------------------------------------------------------------------------------
附1
DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `user_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `course` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `h_score` int(10) DEFAULT NULL, `w_score` int(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; BEGIN; INSERT INTO `t1` VALUES ('张三', '英语', 50, 60); INSERT INTO `t1` VALUES ('张三', '俄语', 70, 80); INSERT INTO `t1` VALUES ('张三', '日语', 80, 90); INSERT INTO `t1` VALUES ('李四', '英语', 45, 55); INSERT INTO `t1` VALUES ('李四', '俄语', 65, 75); INSERT INTO `t1` VALUES ('李四', '日语', 85, 95); COMMIT;
附2
select user_name, 'h_score' as d, sum(case when course = '英语' then h_score else 0 end) as '英语', sum(case when course = '俄语' then h_score else 0 end) as '俄语', sum(case when course = '日语' then h_score else 0 end) as '日语' from t1 GROUP BY user_name union all select user_name, 'wscore' as d, sum(case when course = '英语' then w_score else 0 end) as '英语', sum(case when course = '俄语' then w_score else 0 end) as '俄语', sum(case when course = '日语' then w_score else 0 end) as '日语' from t1 GROUP BY user_name
————————————————
版权声明:本文为CSDN博主「柯南爱上指针」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_35468937/article/details/105496867
#mysql##MySQL#