数据库三张表,学生表,教师表和课程表。
Student (studentId,studentName)
Teacher (teacherId,teacherName)
Course (studentId,teacherId,CourseName)
显示所有满足这样条件的学生名字和老师名字,每个老师只教一个学生,每个学生只由一个老师教,也就是老师和学生一对一。
比如Course表有记录:
1,1,cn1
1,2,cn2
2,1,cn3
3,3,cn4
4,5,cn5
只显示最后两条记录对应的老师名字和学生名字
Student (studentId,studentName)
Teacher (teacherId,teacherName)
Course (studentId,teacherId,CourseName)
显示所有满足这样条件的学生名字和老师名字,每个老师只教一个学生,每个学生只由一个老师教,也就是老师和学生一对一。
比如Course表有记录:
1,1,cn1
1,2,cn2
2,1,cn3
3,3,cn4
4,5,cn5
只显示最后两条记录对应的老师名字和学生名字
select max(studentid) studentid, teacherid from course where teacherid in (
select max(teacherid) from course group by studentid having count(teacherid) = 1)
group by teacherid having count(studentid) = 1) t,
student, teacher
where t.studentid=student.studentid and t.teacherid=teacher.teacherid;
关联姓名