select a.SNAME,AVG(b.SCGRADE)
from S a,SC b
where a.SNO=b.SNO
and a.SNO in(
select SNO
from SC
where SCGRADE<60
group by SNO
having sum(CNO)>2
)
group by a.SNO,a.SNAME
第一个题这样不知道可以不?
from S a,SC b
where a.SNO=b.SNO
and a.SNO in(
select SNO
from SC
where SCGRADE<60
group by SNO
having sum(CNO)>2
)
group by a.SNO,a.SNAME
第一个题这样不知道可以不?
select a.SNAME
from S a,SC b
where a.SNO in(
select SNO
from SC
where SC.CNO=001 OR SC.CNO=002
GROUP BY SNO
HAVING COUNT(SNO)=2
)
group by a.SNO,a.SNAME
select sname from s where sno IN (
select sno from sc where scgrade=(select max(scgrade) from `sc`
where sno in (
select SNO
from SC
where SC.CNO=001 OR SC.CNO=002
GROUP BY SNO
HAVING COUNT(SNO)=2
) and cno=001))
我都经过测试了.第一题,楼上的仁兄回答是正确的...
正确答案应该是:
select a.SNAME,AVG(b.SCGRADE)
from S a,SC b
where a.SNO=b.SNO
and a.SNO in(
select SNO
from SC
where SCGRADE <60
group by SNO
having count(CNO) >=2
)
group by a.SNO,a.SNAME
Note:两门以上.我的理解是包含两门.