mysql数据库行列转换

例1：

思路：

1.建表并插入数据

CREATE table score(
name varchar(11),
Math int,
English int
);
insert into score (name,Math,English) values ('甲',89,78)
insert into score values ('乙',77,81)
insert into score values ('丙',87,98)

2.中间转换，即二维转一维得到一维表

3.利用IF判断并分组即可得到转换的表

select 
c2 as '课程',
sum(if(c1='甲',c3,0)) as '甲',
sum(if(c1='乙',c3,0)) as '乙',
sum(if(c1='丙',c3,0)) as '丙' 
from
((select name as c1,'Math' as c2,Math as c3 from score )
union
(select name,'English' as c2,English from score) ) 
as tx
group by c2

例2：

创建表并添加数据

create table usera(
userid int,
Type int,
Duration_sum int
)
alter table usera modify Duration_sum varchar(30)
insert into usera values (51855025,1,1225)
insert into usera values (51855025,2,2705)
insert into usera values (53711033,1,'......')
insert into usera values (53711033,2,'......')

（省略号代表的是数据。。。。）

select
userid,
sum(if(Type=1,Duration_sum,0)) as Duration_sum_type1,
sum(if(Type=2,Duration_sum,0)) as Duration_sum_type2
from
usera
group by userid