Oracle查询某一科目高于平均分的学生信息

student表

subject表

score表

 问题：查询某一科目高于平均分的学生信息。

1.先查询出该科目的平均成绩

SELECT AVG(SCORENUMBER) FROM SCORE GROUP BY SUBID HAVING SUBID = '30001'

2.把1查询出的结果当做一个值查询出比平均分高的学号

SELECT S.STUID FROM SCORE S WHERE S.SUBID = '30001' AND S.SCORENUMBER >
(SELECT AVG(SCORENUMBER) FROM SCORE GROUP BY SUBID HAVING SUBID = '30001')

3.和student表联合查询得到结果

SELECT S1.* FROM STUDENT S1,
(SELECT S.STUID FROM SCORE S WHERE S.SUBID = '30001' AND S.SCORENUMBER >
(SELECT AVG(SCORENUMBER) FROM SCORE GROUP BY SUBID HAVING SUBID = '30001')) S2 
WHERE S1.STUID = S2.STUID;

结果如下：