扫码加入数据分析学习群

mysql 一维转二维的两种方法

-- 先找出显示的字段

select s_id,

if(c_id='01',score,0),

if(c_id='02',score,0),

if(c_id='03',score,0)

from sc;

-- 再分组 也可以max  ifnull

select s_id,

sum(if(c_id='01',score,0)) as '01',

sum(if(c_id='02',score,0)) as '02',

sum(if(c_id='03',score,0)) as '03'

from sc

group by s_id;-- 按学生编号分组

-- 方法2

select s_id,

c_id='01',-- 符合条件返回1，不符合条件返回0

        c_id='02',

        c_id='03'

from sc;

select s_id,

sum((c_id='01')*score) '01',

        sum((c_id='02')*score) '02',

        sum((c_id='03')*score) '03'

from sc

group by s_id;