如题:
学生成绩表tb1,字段id,cource,score,分别代表学号,课程名,分数,
要求使用一条或多条语句,查出课程的及格人数和不及格人数,
输出为:课程名、及格人数、不及格人数。
select a.cource as 课程名, a.不及格人数, b.及格人数
from (select cource, count(cource) as 不及格人数
from tb1
where score < 60
group by cource) a,
(select cource, count(cource) as 及格人数
from tb1
where score >= 60
group by cource) b
where a.cource = b.cource
附录:
建表语句:
create table tb1 (
id number(10),
cource varchar2(12),
score varchar2(12)
)
插入测试数据
insert into tb1 (ID, COURCE, SCORE)
values (1, '音乐', '60');
insert into tb1 (ID, COURCE, SCORE)
values (2, '音乐', '44');
insert into tb1 (ID, COURCE, SCORE)
values (3, '音乐', '90');
insert into tb1 (ID, COURCE, SCORE)
values (1, '语文', '100');
insert into tb1 (ID, COURCE, SCORE)
values (2, '语文', '40');
insert into tb1 (ID, COURCE, SCORE)
values (3, '语文', '50');
insert into tb1 (ID, COURCE, SCORE)
values (1, '数学', '66');
insert into tb1 (ID, COURCE, SCORE)
values (2, '数学', '43');
insert into tb1 (ID, COURCE, SCORE)
values (3, '数学', '70');