由于最近用到了Access,所以在此记录一下,便于查找。
Sql Server中执行成功的语句:
select t.ExamAddrID, m.[StuNo], p.Photo, cs.SubName
from t_room r
left join dbo.t_examination px on 1=1
left join t_examination_Sub m on m.SubjectID=2 and r.ExamRoomID=m.ExamRoomID and px.seat_no=m.SeatNo
left join t_student_photo p on m.StuNo=p.StuNo
left join t_student s on m.StuNo=s.StuNo
left join t_code_sub cs on s.sex=cs.CodeSubID and cs.CodeID=22
inner join (select distinct examroomid from t_examination_Sub where SubjectID=2) rm on rm.ExamRoomID = r.ExamRoomID;
在Access中修改为:
select t.ExamAddrID, m.[StuNo], p.Photo, cs.SubName
from (((((select * from t_room, t_examination) t
left join (select * from t_examination_Sub where SubjectID=2) m on t.ExamRoomID=m.ExamRoomID and t.seat_no=m.SeatNo)
left join t_student_photo p on m.StuNo=p.StuNo)
left join t_student s on m.StuNo=s.StuNo)
left join (select * from t_code_sub where CodeID=22) cs on s.sex=cs.CodeSubID)
inner join (select distinct examroomid from t_examination_Sub where SubjectID=2) rm on t.ExamRoomID = rm.ExamRoomID;
总结:由此可见,Access在Left join的时候 需要增加 “(” 进行分隔,定值问题需要改写为子查询方式。