請教大家一個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

