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;
结果如下: