select Score.StudentID, Score.Result from (select StudentID from Score where Result > 60) StuAbove60, Teach, Score where StuAbove60.StudentID = Teach.StudentID and Score.StudentID = Teach.ManagerID;
成绩Result大于60分的应该不止一个吧 然后对应的也不止一个班主任咯?select StudentID,Result from Score where StudentID in (select ManagerID from Teach t1 where t1.StudentID in (select StudentID from Score where Result<60) )
如果要求班主任的分数也大于等于60,可以这样写: select a.*,b.managerid from score a left join teach b on a.studentid=b.sutdentid where a.results>=60
select a.StudentID a.Result from Score a, (select q.ManagerID from Teach q, Score t where q.StudentID = t.StudentID and Result > 60) b where a.StudentID = b.ManagerID
SELECT s.STU_ID, s.STU_RESULT, t.MANAGER_ID,s2.stu_result FROM SCORE s INNER JOIN teach t ON S.stu_id = t.stu_id INNER JOIN score s2 ON t.manager_ID = s2.stu_id(之所以内连接 是 因为 一个学生只有一个老师!)
还有个条件,不好意思,应该是这样:SELECT s.STU_ID, s.STU_RESULT, t.MANAGER_ID,s2.stu_result FROM SCORE s INNER JOIN teach t ON S.stu_id = t.stu_id INNER JOIN score s2 ON t.manager_ID = s2.stu_id where s.stu_result > 60;
from
(select StudentID from Score where Result > 60) StuAbove60,
Teach, Score
where StuAbove60.StudentID = Teach.StudentID
and Score.StudentID = Teach.ManagerID;
from Score
where StudentID in
(select ManagerID from Teach t1 where t1.StudentID in
(select StudentID from Score where Result<60)
)
select a.*,b.managerid from score a left join teach b on a.studentid=b.sutdentid where a.results>=60
select a.StudentID a.Result from Score a,
(select q.ManagerID from Teach q, Score t where q.StudentID = t.StudentID and Result > 60) b
where a.StudentID = b.ManagerID
FROM SCORE s
INNER JOIN teach t
ON S.stu_id = t.stu_id
INNER JOIN score s2
ON t.manager_ID = s2.stu_id(之所以内连接 是 因为 一个学生只有一个老师!)
FROM SCORE s
INNER JOIN teach t
ON S.stu_id = t.stu_id
INNER JOIN score s2
ON t.manager_ID = s2.stu_id
where s.stu_result > 60;