mysql 分组后枞表转横表 mysql纵表变横表

一、认识横表与纵表：

横表就是普通的建表方式，如一个表结构为： 主键、字段1、字段2、字段3。。。

如果变成纵表后，则表结构为： 主键、字段代码、字段值，字段代码则为字段1、字段2、字段3。

纵表对从数据库到内存的映射效率是有影响的，但细一点说也要一分为二：

纵表的初始映射要慢一些； 纵表的变更的映射可能要快一些，如果只是改变了单个字段时，毕竟横表字段比纵表要多很多。

1、创建纵表结构 Table_A：

create table Table_A
(
    姓名 varchar(20),
    课程 varchar(20),
    成绩 int
);

2、插入纵表Table_A测试数据：

insert into Table_A(姓名,课程,成绩) values('张三','语文',60);
insert into Table_A(姓名,课程,成绩) values('张三','数学',70);
insert into Table_A(姓名,课程,成绩) values('张三','英语',80);
insert into Table_A(姓名,课程,成绩) values('李四','语文',90);
insert into Table_A(姓名,课程,成绩) values('李四','数学',100);

3、创建横表结构 Table_B：

create table Table_B
(
    姓名 varchar(20),
    语文 int,
    数学 int,
    英语 int
);

4、插入横表Table_B测试数据：

insert into Table_B(姓名,语文,数学,英语) values('张三',60,70,80);
insert into Table_B(姓名,语文,数学,英语) values('李四',90,100,0);

二、纵表变横表

纵表结构 Table_A --> 横表结构 Table_B

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

INSERT INTO table_b
select 姓名,
sum(case 课程 when '语文' then 成绩 else 0 end) as 语文,
sum(case 课程 when '数学' then 成绩 else 0 end) as 数学,
sum(case 课程 when '英语' then 成绩 else 0 end) as 英语
from Table_A
group by 姓名
;

三、横表变纵表

横表结构 Table_B --> 纵表结构 Table_A

方法：union all

INSERT INTO table_A
select 姓名, '语文' as 课程, 语文 as 成绩 from Table_B 
union all
select 姓名, '数学' as 课程, 数学 as 成绩 from Table_B 
union all
select 姓名, '英语' as 课程, 英语 as 成绩 from Table_B
order by 姓名,课程 desc
;

以上。