Mysql-行列转换

@莫等闲

已于 2022-10-12 00:14:34 修改

阅读量328

点赞数

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

于 2022-10-12 00:00:31 首次发布

本文链接：https://blog.csdn.net/Cloud_Wzh/article/details/127274276

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

https://blog.csdn.net/Hudas/article/details/124055641


DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `id` int(20) NOT NULL   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);


SELECT * from t_student ;
-- 列转行

SELECT name,
max(case  when course= "语文" then  score end) 语文,
max(case  when course ="数学" then  score  end) 数学,
max(case  when course ="英语" then  score end) 英语,
max(case  when course ="历史" then  score end) 历史
from t_student ts 
group by ts.name 

-- 行转列
SELECT t.name,"语文" as cource,t.语文 as score from (SELECT name,
													max(case  when course= "语文" then  score end) 语文,
													max(case  when course ="数学" then  score  end) 数学,
													max(case  when course ="英语" then  score end) 英语,
													max(case  when course ="历史" then  score end) 历史
													from t_student 
													group by name ) t
union all 
SELECT t.name,"数学" as cource,t.数学 as score from (SELECT name,
													max(case  when course= "语文" then  score end) 语文,
													max(case  when course ="数学" then  score  end) 数学,
													max(case  when course ="英语" then  score end) 英语,
													max(case  when course ="历史" then  score end) 历史
													from t_student 
													group by name ) t
union all 
SELECT t.name,"英语" as cource,t.英语 as score from (SELECT name,
													max(case  when course= "语文" then  score end) 语文,
													max(case  when course ="数学" then  score  end) 数学,
													max(case  when course ="英语" then  score end) 英语,
													max(case  when course ="历史" then  score end) 历史
													from t_student 
													group by name ) t
union all 
SELECT t.name,"历史" as cource,t.历史 as score from (SELECT name,
													max(case  when course= "语文" then  score end) 语文,
													max(case  when course ="数学" then  score  end) 数学,
													max(case  when course ="英语" then  score end) 英语,
													max(case  when course ="历史" then  score end) 历史
													from t_student 
													group by name ) t

-- 列转多行（字符串）

SELECT name,
group_concat(case  when course= "语文" then  score end) "语文",
group_concat(case  when course ="数学" then  score  end) "数学",
group_concat(case  when course ="英语" then  score end) "英语",
group_concat(case  when course ="历史" then  score end) "历史"
from t_student ts 
group by ts.name 

-- 字符串转换成列: 利用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);