1。嵌套查询: select A.S# from Sc as A, Student as B where B.Sdept = ‘本系’ and A.S# = B.S# and (select count(*) from Sc where S# = A.S#) >= 3 order by A.S# 2。连接查询: select A.S# from Sc as A left join Student as B on A.S# = B.S# where B.Sdept = ‘本系’ and (select count(*) from Sc where S# = A.S#) >= 3 order by A.S#
select s# from student a where sdept='系名' and a.s# in ( select b.s# from sc b group by b.s# having count(*)>=3 )
select a.S# from sc a join student b on a.s#=b.s# where b.sdept='本系' and (select count(c#) from sc where s#=a.s#)>=3
select A.S# from Sc as A, Student as B
where B.Sdept = ‘本系’
and A.S# = B.S#
and (select count(*) from Sc where S# = A.S#) >= 3
order by A.S#
2。连接查询:
select A.S# from Sc as A left join Student as B
on A.S# = B.S#
where B.Sdept = ‘本系’
and (select count(*) from Sc where S# = A.S#) >= 3
order by A.S#
from student a
where sdept='系名' and a.s# in
(
select b.s# from sc b group by b.s# having count(*)>=3
)
from sc a join student b on a.s#=b.s#
where b.sdept='本系' and (select count(c#) from sc where s#=a.s#)>=3