数据:
t1表
id course
1,a
1,b
1,c
1,e
2,a
2,c
2,d
2,f
3,a
3,b
3,c
3,e
根据编写sql,得到结果如下(表中的1表示选修,表中的0表示未选修):
id a b c d e f
1 1 1 1 0 1 0
2 1 0 1 1 0 1
3 1 1 1 0 1 0
利用聚合函数求出1,0
select
id,
sum(case course when 'a' then 1 else 0 end) a,
sum(case course when 'b' then 1 else 0 end) b,
sum(case course when 'c' then 1 else 0 end) c,
sum(case course when 'd' then 1 else 0 end) d,
sum(case course when 'e' then 1 else 0 end) e,
sum(case course when 'f' then 1 else 0 end) f
from
tt1
group by id