多表级联查询问题急！！

来源：百度知道 编辑：UC知道 时间：2024/05/05 09:02:22
现有6个表，下面是创表语句：
1：create table School(id int primary key auto_increment,name varchar(30));
2：create table AcademicYear(id int primary key auto_increment,schoolID int
references School(id),name varchar(30));
3：create table Grade(id int primary key auto_increment,academicYearID int
references AcademicYear(id),name varchar(30));
4：create table Class(id int primary key auto_increment,gradeID int references
Grade(id),name varchar(30));
5：
create table Student(id int primary key auto_increment,name varchar(30),
number varchar(30));
6：create table Student_Class(id int primary key auto_increment,StudentID
int references Student(id),ClassID int references Class(id));

表结构基本上就是这样（要看清表结构，基本上每个表都和另一个表有关联），现在要查Student表中所有的学生和School表中学生对应的学校，用join...on连接查询，请高手写出具体查询语句， 谢谢了！
如果用在hibernate查询中，还需要做那些修改？

SELECT
a.name,
d.name
FROM
student AS a ,
student_class AS b ,
grade AS c ,
school AS d ,
class AS e ,
academicyear AS f
WHERE
a.id = b.StudentID AND
b.ClassID = e.id AND
e.gradeID = c.id AND
c.academicYearID = f.id AND
f.schoolID = d.id

这样写是不是很清晰呢，MYsql已调试通过

----------

考虑到你要的是下面这种格式我又用另一种写法写了一下
SELECT
a.name,
d.name
FROM
student AS a
join student_class AS b on a.id = b.StudentID
join class AS e on b.ClassID = e.id
join grade AS c on e.gradeID = c.id
join academicyear AS f on c.academicYearID = f.id
join school AS d on f.schoolID = d.id

select student.name as 学生姓名,school.name as 学校名称

from
student inner join student_class on sutdent.id=student_class.studentId inner join class on student_class.classid=class.id inner join grade on class.gradeid =grade.id inner join academicyear on gra