查询李老师教的课程学生ID

学生表信息student：sid ,sname,classid;

老师表teacher：tid,tname

课程表course：cid,cname,teacher_id

成绩表score:sid,student_id,corse_id,core_nuber

 

先关联表查询老师教的对应的课程

select * from courser left join teacher on teacher.tid=course.id where teacher.tname="李老师"

筛选出李老师教的课程ID

select cid from courser left join teacher on teacher.tid=course.id where teacher.tname="李老师"

查询成绩表中课程ID在李老师教的课程ID里的所有成绩信息

select * from score where corse_id in(select cid from courser left join teacher on teacher.tid=course.id where teacher.tname="李老师")

因为会有重复的学生ID；所以需要进行去重，则筛选出对于的学生ID：

select student_id from score where corse_id in(select cid from courser left join teacher on teacher.tid=course.id where teacher.tname="李老师") group by student_id;