select count(*) from
student s join course_selection c on s.sno=c.sno left join course co on c.cno=co.cno
where co.name='大学物理' and co.name='大学语文';
name不能重复赋值 但是好像没有替换and的了 应该是用子查询写。但是我想了半天一片白,是要用多表查询套子查询么
设计这个题目的人,估计库表设计规范都不太了解,外键都不设置。
下次问这种问题,麻烦贴一下创建库表的SQL语句!!!
如下,勉强实现了下
1、三张表class、student、score
(1)查询学生SID="1"的成绩级别?
select a.SID,a.Name,a.score from test.student a where
a.SID = '1'
and EXISTS (select 1 from test.score b where a.score BETWEEN b.minScore and b.maxScore);
(2)统计各个班中,成绩级别为优的学生人数?
select count(*) from test.student a
inner join test.class b
on a.classID = b.classID
AND EXISTS (select 1 from test.score c where a.score BETWEEN c.minScore and c.maxScore and level = '优');
2、三张表student、course、course_selection
(1)查询没有选择课程名为“大学物理”的所有学生信息?
select a.*,b.*,c.* from test.student a
inner join test.course_selection b
on a.sno = b.sno
inner join test.course c
on c.cno = b.cno and c.Name != '大学物理';
(2)统计同时学习“大学物理”与“大学语文”的学生人数?
select * from test.student x
where
x.sno in(
select DISTINCT a.sno cname from test.student a
inner join test.course_selection b
on a.sno = b.sno
inner join test.course c
on c.cno = b.cno
AND c.name = '大学语文'
)
AND
x.sno in(
select DISTINCT a.sno from test.student a
inner join test.course_selection b
on a.sno = b.sno
inner join test.course c
on c.cno = b.cno
AND c.name = '大学物理'
);