请教大家一个SQL的问题.

有一个test数据库，里面有一张学员表student，有三个字段，class，name，score，分别表示班级，姓名，成绩，问：查询每个班级的及格人数和不及格人数，输出格式是class，及格人数，不及格人数

这个该怎么写呢?

回复讨论(解决方案)

select class,sum(case score >=60 then 1 else 0 end) as '及格人数',sum(case score < 60 then 1 else 0 end) as '不及格人数'from student group by class

 

select class,(select count(*) from student where class=stu.class and score >=60 ) as '及格人数',(select count(*) from student where class=stu.class and score <60 ) as '及格人数',from student stu group by class

哦，写错了。更正：

select class,(select count(*) from student where class=stu.class and score >=60 ) as '及格人数',(select count(*) from student where class=stu.class and score <60 ) as '不及格人数',from student stu group by class