本文实例讲述了mysql横纵表相互转化操作实现方法。分享给大家供大家参考，具体如下：
先创建一个成绩表（纵表）
|
1
2
3
4
5
6
7
8
9
10
11
|
create table user_score
(
name varchar(20),
subjects varchar(20),
score int
);
insert into user_score(name,subjects,score) values('张三','语文',60);
insert into user_score(name,subjects,score) values('张三','数学',70);
insert into user_score(name,subjects,score) values('张三','英语',80);
insert into user_score(name,subjects,score) values('李四','语文',90);
insert into user_score(name,subjects,score) values('李四','数学',100);
|
再创建一个成绩表（横表）
|
1
2
3
4
5
6
7
8
9
|
create table user_score2
(
name varchar(20),
yuwen int,
shuxue int,
yingyu int
);
insert into user_score2(name,yuwen,shuxue,yingyu) values('张三',60,70,80);
insert into user_score2(name,yuwen,shuxue,yingyu) values('李四',90,100,0);
|
纵表转横表
|
1
2
3
4
|
select name,sum(case subjects when '语文' then score else 0 end)
as '语文',sum(case subjects when '数学' then score else 0 end)
as '数学', sum(case subjects when '英语' then score else 0 end)
as '英语'from user_score group by name;
|
纵表转横表
|
1
2
3
4
|
select name,'yuwen' as subjects,yuwen as score from user_score2 union all
select name,'shuxue' as subjects,shuxue as score from user_score2 union all
select name,'yingyu' as subjects,yingyu as score from user_score2
order by name,subjects desc;
|
希望本文所述对大家MySQL数据库计有所帮助。
原文链接：https://blog.csdn.net/huangyuxin_/article/details/79678719