求一SQL语句!

[mooninday]

现有4张表:
studentmain:学生表
student_id,student_name
studentscore:分数表
student_id,test_id,student_score
subjectmain:学科表
subject_id,subject_name
testschedule:考试日程表
test_id,subject_id,test_date

现在要求是:查询出 每个学生的每门课程的最近一次考试的分数(即使该课程没有分数,也要查询出来)

[klan]

try:

select g.student_name, h.subject_name,ifnull(f.student_score,0) score from
(select a.student_id, b.subject_id from studentmain a , subjectmain b)e left outer join (
select c.student_id, c.student_score, d.subject_id from studentscore c inner join (
select max(test_Id) testID,subject_Id from testschedule group by subject_Id) d
on c.test_id=d.testId)f on e.student_id=f.student_id and e.subject_id=f.subject_id
inner join studentmain g on e.student_id=g.student_id
inner join subjectmain h on e.student_id=h.subject_id;

[懒得去死]

select a.student_name,b.subject_name,c.student_score from studentmain a, subjectname b, studentscore c, testschedule d
where a.student_id = b.student_id and b.subject_id = c.subject_id order by d.test_date;