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，

select sum(case when 成绩>=60 then 1 else 0 end) as 及格人数,sum(case when 成绩<60 then 1 else 0 end )as 不及格人数,sum(case when 成绩 is null then 1 else 0 end )as 没有成绩人数 from 成绩表

两种方法
select case when score>60 then '及格' else '不及格' end as '类别',count (*) as '人数' from db_score
group by case when score>60 then '及格' else '不及格' end

-------------------------------------

select '不及格' as '类别' ,count(*)as '人数' from db_score where score<60
union all
select '及格' as '类别' ,count(*) as '人数' from db_score where score>=60