博客

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;

原理同列转行