mysql行转列和列转行

最新推荐文章于 2024-06-14 10:14:22 发布

shy好好学习

最新推荐文章于 2024-06-14 10:14:22 发布

阅读量831

点赞数

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

本文链接：https://blog.csdn.net/shy_1762538422/article/details/122349331

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

行转列

1、初始数据

在这里插入图片描述

2、行转列后的数据

在这里插入图片描述

3、sql语句

select name, 
max(CASE type WHEN '语文' THEN score.score else 0 END) '语文',
max(CASE type WHEN '数学' THEN score.score else 0 END) '数学',
max(CASE type WHEN '英语' THEN score.score else 0 END) '英语'
from score GROUP BY name;

这里使用max函数的原因是因为group by分组，没有max函数的话他就会查询每个分组的第一个元组，所以max函数是不能少的。

列转行

1、初始数据

在这里插入图片描述

2、列转行后的数据

在这里插入图片描述

3、sql语句

select id,name, '语文' as course, chinese as score from score01
UNION ALL
select id,name, '数学' as course, math as score from score01
UNION ALL
select id,name, '英语' as course, english as score from score01
order by id

全部sql

==============================================行转列==============================================
CREATE TABLE `score` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT into score VALUES(1,'张三','语文',70);
INSERT into score VALUES(2,'张三','数学',80);
INSERT into score VALUES(3,'张三','英语',90);
INSERT into score VALUES(4,'李四','语文',50);
INSERT into score VALUES(5,'李四','数学',60);
INSERT into score VALUES(6,'李四','英语',80);
INSERT into score VALUES(7,'王五','语文',60);
INSERT into score VALUES(8,'王五','数学',90);
INSERT into score VALUES(9,'王五','英语',80);

select * from score;

select name, 
max(CASE type WHEN '语文' THEN score.score else 0 END) '语文',
max(CASE type WHEN '数学' THEN score.score else 0 END) '数学',
max(CASE type WHEN '英语' THEN score.score else 0 END) '英语'
from score GROUP BY name;
 
==============================================列转行==============================================

CREATE TABLE `score01` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `chinese` int DEFAULT NULL,
  `math` int DEFAULT NULL,
  `english` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO score01 VALUES(1,'张三',50,60,70);
INSERT INTO score01 VALUES(2,'李四',80,60,50);
INSERT INTO score01 VALUES(3,'王五',80,70,60);

select * from score01;

select id,name, '语文' as course, chinese as score from score01
UNION ALL
select id,name, '数学' as course, math as score from score01
UNION ALL
select id,name, '英语' as course, english as score from score01
order by id;