当前位置:Gxlcms > PHP教程 > 请教大家一个SQL的问题.

请教大家一个SQL的问题.

时间:2021-07-01 10:21:17 帮助过:3人阅读

有一个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

人气教程排行