mysql的行列转换

概述

今天主要做一个实验，先理解下mysql行列转换，后面在做一下反向的行列转换。

需求

二维关系转换为三维关系。

1、环境准备create database test;use test;create table t_score(id int primary key auto_increment,name varchar(20) not null, #名字Subject varchar(10) not null, #科目Fraction double default 0 #分数);ｉｎｓｅｒｔ INTO `t_score`(name,Subject,Fraction) VALUES (‘王海‘, ‘语文‘, 86), (‘王海‘, ‘数学‘, 83), (‘王海‘, ‘英语‘, 93), (‘陶俊‘, ‘语文‘, 88), (‘陶俊‘, ‘数学‘, 84), (‘陶俊‘, ‘英语‘, 94), (‘刘可‘, ‘语文‘, 80), (‘刘可‘, ‘数学‘, 86), (‘刘可‘, ‘英语‘, 88), (‘李春‘, ‘语文‘, 89), (‘李春‘, ‘数学‘, 80), (‘李春‘, ‘英语‘, 87);

2、实现方案一--使用ifｓｅｌｅｃｔ name as 名字 ,sum(if(Subject=‘语文‘,Fraction,0)) as 语文,sum(if(Subject=‘数学‘,Fraction,0))as 数学, sum(if(Subject=‘英语‘,Fraction,0))as 英语,round(AVG(Fraction),2) as 平均分,SUM(Fraction) as 总分from t_score group by name unionselect name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(ｓｅｌｅｃｔ ‘TOTAL‘ as name,sum(if(Subject=‘语文‘,Fraction,0)) as 语文,sum(if(Subject=‘数学‘,Fraction,0))as 数学, sum(if(Subject=‘英语‘,Fraction,0))as 英语,SUM(Fraction) as 总分from t_score group by Subject )t

3、实现方案二--使用caseｓｅｌｅｃｔ name as Name,sum(case when Subject = ‘语文‘ then Fraction end) as Chinese,sum(case when Subject = ‘数学‘ then Fraction end) as Math,sum(case when Subject = ‘英语‘ then Fraction end) as English,round(AVG(Fraction),2) as 平均分,sum(fraction)as scorefrom t_score group by nameUNION ALLselect name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(ｓｅｌｅｃｔ ‘TOTAL‘ as name,sum(case when Subject = ‘语文‘ then Fraction end) as Chinese,sum(case when Subject = ‘数学‘ then Fraction end) as Math,sum(case when Subject = ‘英语‘ then Fraction end) as English,sum(fraction)as scorefrom t_score group by Subject)t;

4、实现方案三--使用maxｓｅｌｅｃｔ name as Name,max(case when Subject = ‘语文‘ then Fraction end) as Chinese,max(case when Subject = ‘数学‘ then Fraction end) as Math,max(case when Subject = ‘英语‘ then Fraction end) as English,sum(fraction)as scorefrom t_score group by nameUNION ALLselect name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(ｓｅｌｅｃｔ ‘TOTAL‘ as name,max(case when Subject = ‘语文‘ then Fraction end) as Chinese,max(case when Subject = ‘数学‘ then Fraction end) as Math,max(case when Subject = ‘英语‘ then Fraction end) as English,sum(fraction)as scorefrom t_score group by Subject)t;

5、实现方案四--with rollup

group by 后可以跟with rollup，表示在进行分组统计的基础上再次进行汇总统计（在每个分组下都会有统计汇总）

ｓｅｌｅｃｔ ifnull(name,‘TOTAL‘) name,sum(if(Subject=‘语文‘,Fraction,0)) as 语文,sum(if(Subject=‘英语‘,Fraction,0)) as 英语,sum(if(Subject=‘数学‘,Fraction,0))as 数学,sum(Fraction) 总分from t_score group by name with rollup;​create table t_all as ｓｅｌｅｃｔ coalesce(name,‘TOTAL‘) name,sum(if(Subject=‘语文‘,Fraction,0)) as 语文,sum(if(Subject=‘英语‘,Fraction,0)) as 英语,sum(if(Subject=‘数学‘,Fraction,0))as 数学,sum(Fraction) 总分from t_score group by name with rollup;

6、反向行列转换

有时我们业务部门会给我们一张excel表，一般都是三维关系的，如果想要导进数据库变成两张二维关系的表去join，应该怎么实现呢？类似：

实现方案：最简单的union

ｓｅｌｅｃｔ Name as name,‘语文‘ as Subject,Chinese as Farction from t_all where Name!=‘TOTAL‘union ｓｅｌｅｃｔ Name as name,‘数学‘ as Subject,Math as Farction from t_all where Name!=‘TOTAL‘union ｓｅｌｅｃｔ Name as name,‘英语‘ as Subject,English as Farction from t_all where Name!=‘TOTAL‘

建议大家抽空可以做一下，这个还是很有用的。觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~