mysql行转列列转行在线求助！！！！！！！！！！

最新推荐文章于 2024-08-11 15:10:04 发布

柯南爱上指针

最新推荐文章于 2024-08-11 15:10:04 发布

阅读量329

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_35468937/article/details/105496867

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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