用SQL语句三种方法查询秋季学期有2门以上课程获90分以上成绩的学生名

Question

基表
STUDENT
SNO SNAME
0908222 王然。。。
COURSE
CNO SEMESTER
CS-100 秋。。。
SC
SNO CNO GRADE
0908222 CS-100 95
我想知道的是如何表示有2门以上课程获90分以上

Answer 1

方法一:连接
select distinct sname from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno
and grade>=90 and semester='秋'
group by student.sno having count(*)>=2

方法二:嵌套
select sname from student where sno in(
select sno from sc where grade>=90 and cno in(
select cno from course where semester='秋')
group by sno having count(*)>=2)

主要是以上两种方法,其它方法都是用以上两种方法演变过来,这里主要用group by sno 对每个学生进行分组,然后用having count(*)>=2对每组进行筛选.

方法三:
select sname from student,(
select sno from sc where grade>=90 and cno in(
select cno from course where semester='秋')
group by sno having count(*)>=2) t where student.sno=t.sno

方法四:
select sname from student where exists(
select * from sc where (
select count(*) from sc where sno=student.sno and grade>=90 and cno in
(select cno from course where semester='秋'))>=2)

类似的方法有很多,主要是连接法和嵌套法.

Answer 2

1.select SNAME from STUDENT where SNO in
(select SNO from SC where GRADE>90 group by SNO having count(*)>=2)

2.select a.SNAME from STUDENT a,(select SNO from SC where GRADE>90 group by SNO having count(*)>=2) b
where a.SNO=B.SNO

3.select a.SNAME from STUDENT a,SC b where a.SNO=b.SNO and b.GRADE>90
group by a.SNAME having count(*)>2

解释：select SNO from SC where GRADE>90 group by SNO having count(*)>=2 这句就代表按照SNO汇总，分数90以上，并且条数（也就是有两科）也是2门以上（包含2门），如果只是2门以上，你可以把=号去掉本回答被提问者采纳

Answer 3

第一种：
select t1.sname from STUDENT t1 where (select count(*) from SC t2 where t2.sno=t1.sno and
t2.grade>90)>2

第二种：
select t1.sname from STUDENT t1 where t1.sno in
(select sno from SC
where (select count(*) from SC t2
where t2.sno=t1.sno
and t2.grade>90
)>2
)

第三种：
select t1.sname from STUDENT t1
where t1.sno in
(select t2.sno from SC t2 where t2.grade>90 group by t2.sno having count(*)>2)

楼上的写的不错，呵呵

Answer 4

特别推荐：
select t.*, c.*, s.* from
student t , course c , sc s where t.sno = s.sno and c.cno = s.cno
and s.sno in ( select sno from (
select sno ,count(*) from sc where grade > 90 group by sno having count(*) > 1 ))

Answer 5

select *
from student A,
(select sno
from sc
where grade > 90
group by sno
having count(sno) > 1) B
where A.sno = B.sno