行转列

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;

一、行转列

数据准备

建表：

CREATE TABLE `student` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(20) DEFAULT NULL,
  `COURSE` varchar(20) DEFAULT NULL,
  `SCORE` float DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

新增数据：

insert into student(USER_NAME, COURSE, SCORE)  values
("张三", "数学", 34),
("张三", "语文", 58),
("张三", "英语", 58),
("李四", "数学", 45),
("李四", "语文", 87),
("李四", "英语", 45),
("王五", "数学", 76),
("王五", "语文", 34),
("王五", "英语", 89);

源数据：

SELECT user_name ,
    MAX(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
    MAX(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
    MAX(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语
FROM student
GROUP BY USER_NAME;

行转列后：

二、列转行

数据准备

建表：

CREATE TABLE `grade` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(20) DEFAULT NULL,
  `CN_SCORE` float DEFAULT NULL,
  `MATH_SCORE` float DEFAULT NULL,
  `EN_SCORE` float DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

新增数据：

insert into grade(USER_NAME, CN_SCORE, MATH_SCORE, EN_SCORE) values
("张三", 34, 58, 58),
("李四", 45, 87, 45),
("王五", 76, 34, 89);

源数据：

select user_name, '语文' COURSE , CN_SCORE as SCORE from grade
union select user_name, '数学' COURSE, MATH_SCORE as SCORE from grade
union select user_name, '英语' COURSE, EN_SCORE as SCORE from grade
order by user_name,COURSE;

列转行后：

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 ;