rollup（你所需要的关于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 #分数 ); INSERT INTO `t_score`(name,Subject,Fraction) VALUES ('王海', '语文', 86), ('王海', '数学', 83), ('王海', '英语', 93), ('陶俊', '语文', 88), ('陶俊', '数学', 84), ('陶俊', '英语', 94), ('刘可', '语文', 80), ('刘可', '数学', 86), ('刘可', '英语', 88), ('李春', '语文', 89), ('李春', '数学', 80), ('李春', '英语', 87);

2、实现方案一--使用if

select 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 union select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from( select '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

select 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 score from t_score group by name UNION ALL select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from( select '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 score from t_score group by Subject)t;

4、实现方案三--使用max

select 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 score from t_score group by name UNION ALL select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from( select '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 score from t_score group by Subject)t;

5、实现方案四--with rollup

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

select 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 select 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

select Name as name,'语文' as Subject,Chinese as Farction from t_all where Name!='TOTAL' union select Name as name,'数学' as Subject,Math as Farction from t_all where Name!='TOTAL' union select Name as name,'英语' as Subject,English as Farction from t_all where Name!='TOTAL'

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