sql行转列三个方法

1.行转列sum+if/case when

由多行变一行，group by聚合，由一列变多列，衍生提前。

select uid, sum(if(course=‘语文’,score,NULL)) as 语文,

sum((if(course=‘数学’,score,NULL)) as 数学

from score group by uid;

2.列转行union

select uid, ‘语文’ as course，语文 as score

from score where 语文 is not null

union

select uid, ‘数学’ as course, 数学 as score

from score where 数学 is not null

‘字符串常量’ 课程名

3.自连接

select c.*,a.s_score s01,b.s_score s02

from score a, score b, student c

where

a.c_id=‘01’

and b.c_id=‘02’

and a.s_id=b.s_id

andc.s_id=a.s_id

and a.s_score>b.s_score;