【学亮IT手记】MySql行列转换案例

create table score(
    name varchar(10),
    math int,
    english int
);
insert into score VALUES('甲',100,92);
insert into score VALUES('乙',85,98);
insert into score VALUES('丙',90,95);
insert into score VALUES('丁',88,96);
SHOW tables;
SELECT * from score;

--行列转换：
select 
c2 as '课程',
SUM(if(c1='甲',c3,0)) '甲',
SUM(if(c1='乙',c3,0)) '乙',
SUM(if(c1='丙',c3,0)) '丙',
SUM(if(c1='丁',c3,0)) '丁'
from(
select name c1,'math' c2,math c3 from score GROUP BY name
union 
select name,'english' as c2,english from score GROUP BY name
)tt 
group by c2