Question

sql语句查询数据是和三张表有关联的应该怎么写?

比如说:
有三张表School,Class,Student
三张表各自的字段分别如下,
School:SchoolId,ClassId;
Class:ClassId,StudentId;
Student:StudentId,StudentName;

然后我希望经过SELECT查询得到SchoolID和StudentName两个字段
而其中表Student和表Class的StudentId要对应,以及表Class和表School的ClassId也要对应..

也就是说条件是:Student.StudentId = Class.Student
Class.ClassId = School.ClassId

请问这个应该怎么写?

Answer 1

select School.SchoolID,Student.StudentName
from School full join Class on School.ClassId=Class.ClassId full join Student on Class.StudentId=Student.StudentId

要查找的东西放在最上面，然后连接表，连接条件。就这样。

Answer 2

select School.SchoolID,Student.StudentNamefrom Student left join Class on Student.StudentId = Class.Student left join School on Class.ClassId = School.ClassId

Answer 3

select schoolID,StudentName from school join class on school.classid=class.classid join student on class.studentid=student.studentid
或者
select schoolID,StudentName from school,class,student where school.classid=class.classid and class.studentid=student.studentid

Answer 4

SELECT SCHOOLID,STUDENTNAME FROM SCHOOL,CLASS,STUDENT
WHERE SCHOOL.CLASSID=CLASS.CLASSID AND CLASS.STUDENTID=STUDENT.STUDENTID