mysql中横竖转换_MySQL中横表和竖表相互转换

一  竖表转横表

1. 首先创建竖表

create table student (

id varchar(32) primary key,

name varchar (50) not null,

subject varchar(50) not null,

result int);

2.  插入数据

insert into student (id, name, subject, result) values ('0001', '小明', '语文', 83);

insert into student (id, name, subject, result) values ('0002', '小明', '数学', 97);

insert into student (id, name, subject, result) values ('0003', '小明', '英语', 93);

insert into student (id, name, subject, result) values ('0004'', '小白', '语文', 93);

insert into student (id, name, subject, result) values ('0005', '小白', '数学', 93);

insert into student (id, name, subject, result) values ('0006', '小白', '英语', 95);

3. 查询数据

select * from student;

4. 想要将数据显示为横表, 即如下形式

废话不多说了, 直接上sql语句:

select name as '姓名',

max(case subject when '语文' then result else 0 end) '语文',

max(case subject when '数学' then result else 0 end) '数学',

max(case subject when '英语' then result else 0 end) '英语'

from student group by name;

二, 横表变竖表

1.  创建横表

create table student1 (

id varchar(32) primary key,

姓名 varchar(50) not null,

语文 int,

数学 int,

物理 int

);

2.  插入数据

insert into student1 (id, 姓名, 语文, 数学, 物理) values ('0001','小张', 93, 84, 99);

insert into student1 (id, 姓名, 语文, 数学, 物理) values ('0002','小马', 86, 92, 90);

3.  查询数据

select * from student1;

4. 将横表数据转换为竖表的形式, 即如下形式:

对应的sql语句:

select*from

(

select 姓名 as Name , '语文' as Subject , 语文 as result from student1

union all

select 姓名 as Name , '数学' as Subject , 数学 as result from student1

union all

select 姓名 as Name , '物理' as Subject , 物理 as result from student1

) t

order by name;