用SQL语句三种方法查询秋季学期有2门以上课程获90分以上成绩的学生名
发布网友
发布时间:2022-04-07 20:00
我来回答
共6个回答
热心网友
时间:2022-04-07 21:30
方法一:连接
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)
类似的方法有很多,主要是连接法和嵌套法.
热心网友
时间:2022-04-07 22:48
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门以上,你可以把=号去掉
热心网友
时间:2022-04-08 00:22
第一种:
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)
楼上的写的不错,呵呵
热心网友
时间:2022-04-08 02:14
特别推荐:
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 ))
热心网友
时间:2022-04-08 04:22
select *
from student A,
(select sno
from sc
where grade > 90
group by sno
having count(sno) > 1) B
where A.sno = B.sno
热心网友
时间:2022-04-08 06:46
select sno, count(*) from sc where grade>90 group by sno having count(*)>2;