Mysql 行转列、列转行

最新推荐文章于 2025-03-31 08:25:16 发布

草原的蚂蚱

最新推荐文章于 2025-03-31 08:25:16 发布

阅读量663

点赞数

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

本文链接：https://blog.youkuaiyun.com/huang812561/article/details/124708788

版权

MySql 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了如何使用SQL进行数据转换，包括将行数据转换为列数据（pivot操作）和将列数据转换为行数据（unpivot操作）。通过示例展示了创建表、插入数据、查询原始数据，以及具体的SQL语句实现，帮助理解这两种转换方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 行转列

建表语句

CREATE table student(
id int(8) primary key auto_increment,
name varchar(50) default null comment '姓名',
subject varchar(20) default null comment '科目',
score double default 0 comment '分数'
);

测试数据

INSERT into student (name,subject,score) values ('张三','语文',99);
INSERT into student (name,subject,score) values ('张三','数学',100);
INSERT into student (name,subject,score) values ('张三','英语',87);
INSERT into student (name,subject,score) values ('李四','语文',89);
INSERT into student (name,subject,score) values ('李四','数学',97);
INSERT into student (name,subject,score) values ('李四','英语',88);

原始数据查询

SELECT * from student ;

行转列SQL语句：

SELECT name , MAX(CASE subject when '语文' then score else 0 end) as '语文', MAX(CASE subject when '数学' then score else 0 end) as '数学', MAX(CASE subject when '英语' then score else 0 end) as '英语' FROM student group by name;

2. 列转行

建表语句

CREATE table student2(
id int(8) primary key auto_increment,
name varchar(50) default null comment '姓名',
subject1 varchar(20) default null comment '语文',
subject2 varchar(20) default null comment '英语',
subject3 varchar(20) default null comment '英语'
);

测试数据

insert into student2 (name,subject1,subject2,subject3) values('张三','99','100','87');
insert into student2 (name,subject1,subject2,subject3) values('李四','89','97','88');

原始数据查询

SELECT * from student2 ;

列转行SQL语句：

两种方式：

SELECT name,'语文' as subject , subject1 as score from student2
UNION
SELECT name,'数学' as subject , subject2 as score from student2
UNION
SELECT name,'英语' as subject , subject3 as score from student2
order by name ;

或

SELECT
   name ,
   '语文' as subject,
   MAX(subject1) as score
from
   student2
group by
   name
UNION
SELECT
   name ,
   '数学' as subject,
   MAX(subject2) as score
from
   student2
group by
   name
UNION
SELECT
   name ,
   '英语' as subject,
   MAX(subject3) as score
from
   student2
group by
   name
ORDER BY
   name ;