--如: Select a.* from Student as a where exists(Select stuID from score where curriculum<>'體育' and stuid=a.id group by stuid having avg(score)>85)
select s1.* from student s1, score s2 where s1.id=s2.stuID and s2.curriculum<>'体育') group by s1.id,s1.name,s1.sex having avg(s2.score)>85
SELECT * FROM [SCORE] WHERE STUID IN ( SELECT STUID FROM [SCORE] WHERE CURRICULUM<>'体育' GROUP BY STUID HAVING AVG(SCORE)>=85 )
select * from student where id in (select stuID from score where curriculum='體育' group by stuID having sum(score)>85)
select * from student where id in (select stuID from score where curriculum<>'體育' group by stuID having sum(score)>85)
select student.* from student, score where student.id=score.stuID and score.curriculum<>'体育') group by student.id having avg(score.score)>85
表student字段:id name sex 表score字段:id stuID curriculum score現在要查詢:每個學生的平均分大於85的學生信息,但是不包含“體育”成績RE: select * from student a inner join (select id,avg(score) score from score where curriculum<>'體育' group by id having avg(score)>85) b on a.id=b.id
Select a.* from Student as a
where exists(Select stuID from score
where curriculum<>'體育' and stuid=a.id
group by stuid having avg(score)>85)
s1.*
from
student s1,
score s2
where
s1.id=s2.stuID and s2.curriculum<>'体育')
group by
s1.id,s1.name,s1.sex
having
avg(s2.score)>85
(
SELECT STUID FROM [SCORE] WHERE CURRICULUM<>'体育' GROUP BY STUID HAVING AVG(SCORE)>=85
)
where id in
(select stuID from score where curriculum='體育'
group by stuID
having sum(score)>85)
where id in
(select stuID from score where curriculum<>'體育'
group by stuID
having sum(score)>85)
where student.id=score.stuID and score.curriculum<>'体育')
group by student.id having avg(score.score)>85
表score字段:id stuID curriculum score現在要查詢:每個學生的平均分大於85的學生信息,但是不包含“體育”成績RE:
select *
from student a
inner join (select id,avg(score) score from score where curriculum<>'體育' group by id having avg(score)>85) b
on a.id=b.id