SQL查询出平均分大于80分,且至少两门课大于90的学生。

发布网友发布时间：2022-04-10 05:40

共3个回答

热心网友时间：2022-04-10 07:10

select a.* from stu a,study b,(select a.Sno Sno,count(b.Grade) shuliang from stu a,study b where a.Sno=b.Sno and b.Grade>=90 group by a.Sno)t
where a.Sno=b.Sno and a.Sno=t.Sno and t.shuliang>=2
group by a.Sno,a.Sname,a.Sex,a.Age,a.Native
having avg(b.Grade)>=80

热心网友时间：2022-04-10 08:28

select a.* from stu a,study b
where a.SNo=b.SNo and max(b.Grade)>90 and b.Grade not in(
select b.Grade from stu a,study b
where a.SNo=b.SNo and max(d.Grade)>90
) b.SNo in(
select SNo from (select b.Grade,a.SNo from stu a,study b
where a.SNo=b.SNo) group by SNo having avg(Grade)>80);

热心网友时间：2022-04-10 10:02

select * from stu where sno in (select sno from study where avg(grade) > 80 where count(grade>80) >= 2 order by sno)