用SQL语句三种方法查询秋季学期有2门以上课程获90分以上成绩的学生名
STUDENT
SNO SNAME
0908222 王然。。。
COURSE
CNO SEMESTER
CS-100 秋。。。
SC
SNO CNO GRADE
0908222 CS-100 95
我想知道的是如何表示有2门以上课程获90分以上
最新回答
号订婚
2024-11-25 13:18:44
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)
类似的方法有很多,主要是连接法和嵌套法.
一抹晚夏
2024-11-25 08:23:21
(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门以上，你可以把=号去掉
暖妹
2024-11-25 12:38:50
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)
楼上的写的不错，呵呵
因为太帅被罚块
2024-11-25 13:26:32
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 ))
一川绿风
2024-11-25 09:52:47
from student A,
(select sno
from sc
where grade > 90
group by sno
having count(sno) > 1) B
where A.sno = B.sno
热门标签
