纵表、横表互转

发表于 更新于 分类于

mysql中横表和纵表的互换。

将纵表中的多行记录转化成横表中的一条记录。

1、 建表

纵表结构：

1
2
3
4
5
6
7
8
9
10
11
create table Table_A
(
    name varchar(20),
    course varchar(20),
    score int
);
insert into Table_A(name,course,score) values('zhangsan','chinese',60);
insert into Table_A(name,course,score) values('zhangsan','math',70);
insert into Table_A(name,course,score) values('zhangsan','english',80);
insert into Table_A(name,course,score) values('lisi','chinese',90);
insert into Table_A(name,course,score) values('lisi','math',100);

横表结构

1
2
3
4
5
6
7
8
9
create table Table_B
(
    name varchar(20),
    chinese int,
    math int,
    english int
);
insert into Table_B(name,chinese,math,english) values('zhangsan',60,70,80);
insert into Table_B(name,chinese,math,english) values('lisi',90,100,0);

2、纵表变横表

方法一：聚合函数[max或sum]配合case语句

1
2
3
4
5
6
select name,
sum (case course when 'chinese' then score else 0 end) as chinese,
sum (case course when 'math' then score else 0 end) as math,
sum (case course when 'english' then score else 0 end) as english
from Table_A
group by name;

方法二：使用pivot

1
select * from Table_A pivot (max(score)for course in(chinese,math,english)) tmp_table;

3、横表变纵表

方法一：union all

1
2
3
4
select name,'chinese' as course,chinese as score from Table_B union all
select name,'math' as course,math as score from Table_B union all
select name,'english' as course,english as score from Table_B
order by name,course desc;

方法二：使用unpivot

1
2
3
select name,course,score from Table_B
unpivot
(score for course in ([chinese],[math],english)) tmp_table;

参考文献

纵表、横表互转的SQL