#查询第二名和第三名的学生信息;

select * from sc sc1

where (select sum(score>sc1.score) from sc where c_id=sc1.c_id) in (1,2)

#查询每门课程的平均成绩，并且平均成绩大于等于70分：

select avg(score)平均成绩,c_id from sc

group by c_id

having 平均成绩>=70;

#查询至少选修了2门课的学生学号：

select s_id from sc

group by s_id

having count(*)>=2;

#查询至少两门课程及格的学生学号：

select s_id from sc

group by s_id

having sum(score>=60)>1;

将成绩表转换为二维表的三种方式：

select s_id,

sum(if(c_id=01,score,0)) "01",

sum(if(c_id=02,score,0)) "02",

sum(if(c_id=03,score,0)) "03“

from sc

group by s_id;

select s_id,

sum(case when c_id=01 then score else 0 end) "01",

sum(case when c_id=02 then score else 0 end) "02",

sum(case when c_id=03 then score else 0 end) "03“

from sc

group by s_id;

# 工作中常用

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;