SQL面试题常见题

3、下表是每个课程 class_id 对应的年级(共有 primary、middle、high 三个)，以及某种比率 rate

class_id grade rate
abc123 primary 70%
abc123 middle 65%
abc123 high 72%
hjkk86 primary 69%
hjkk86 middle 63%
hjkk86 high 74%

请写出 SQL 查询出如下形式的表：

class_id primary middle high
abc123 70% 65% 72%
hjkk86 69% 63% 74%

select
class_id,
max(if(grade='primary', rate, 0)) as 'primary',
max(if(grade='middle', rate, 0)) as 'middle',
max(if(grade='high', rate, 0)) as 'high'
from table 
group by class_id