SQL 查询某门课程及格的总人数以及不及格的总人数以及没成绩的人数

1、创建测试表，

create table test_score(class_id varchar2(20), student_id varchar2(20), score number)

2、插入测试数据

insert into test_score values ('C07', 1001, 50)

insert into test_score values ('C07', 1002, 72)

insert into test_score values ('C07', 1003, 85)

insert into test_score values ('C07', 1004, 91)

insert into test_score values ('C07', 1005, 48)

insert into test_score values ('C07', 1006, 79)

insert into test_score values ('C07', 1007, null)

3、查询表的记录，select t.*, rowid from test_score t

4、编写sql，查询某某课程及格的总人数以及不及格的总人数以及没成绩的人数，

select class_id,

     count(distinct case when score <60 then student_id end) s1,

     count(distinct case when score >= 60 then student_id end) s2,

   count(distinct case when score is null then student_id end) s3

from test_score t group by class_id，

---以下在SQL2005执行通过--

---结果将以 科目、及格数、不及格数 显示

select * from

(select col2,count(*) as [及格数]

from tb

where col1>=60

group by col2

)t

outer apply

(select count(*) as [不及格数]

from tb

where col1<60 and t.col2= col2

group by col2

)m

-----这应该是楼主想要的了吧。

假设表有如下几列：班级、学号、姓名、成绩，且大于等于60分为及格，则

select 班级, count(distinct if(成绩>=60, 学号, null)) as 及格人数

from 成绩表

group by 班级

---