我的查询如下:
SELECT * FROM `C_Institute`
WHERE `ID` IN
(SELECT `instituteID` FROM `C_Faculty` WHERE `ID`
IN (SELECT `facultyID` FROM `C_EducationalGroup` WHERE `ID`
IN (SELECT `educationalGroupID` FROM `C_StudyField` WHERE `ID`
IN (SELECT `studyFieldID` FROM `b_PersonEmployment` WHERE `personID`=1 ORDER BY `startDate` limit 1) )));
但是MySQL不支持子查询的limit
和in
。我不知道如何编写此查询。 谁能帮我?
提前致谢
您可以重写此查询一系列连接:
SELECT c1.*
FROM C_Institute c1
INNER JOIN C_Faculty c2
ON c1.ID = c2.instituteID
INNER JOIN C_EducationalGroup c3
ON c2.ID = c3.facultyID
INNER JOIN C_StudyField c4
ON c3.ID = c4.educationalGroupID
INNER JOIN b_PersonEmployment b
ON c4.ID = b.studyFieldID
WHERE b.personID = 1
ORDER BY b.startDate
LIMIT 1;
假设每个表中的 ID 都引用另一个表,
试试这个,让我知道:
select * from C_Institute c join C_Faculty f on c.ID=F.instituteID left join C_EducationalGroup e on f.ID=e.facultyID left join C_StudyField sf on e.ID=sf.educationalGroupID left join b_PersonEmployment p on sf.ID=p.studyFieldID where p.personID=1 order by p.startDate limit 1;
此外,分享您的表格描述以获取更多线索
感谢您的所有回答。 但我认为加入不是解决这个问题的好方法。我已经通过两个查询完成了它(也许不是好主意,但比加入更好(:
SET @studyFieldID =(SELECT `studyFieldID` FROM `b_PersonEmployment` WHERE `personID`=1 ORDER BY `startDate` limit 1);
SELECT * FROM `C_Institute`
WHERE `ID` IN
(SELECT `instituteID` FROM `C_Faculty` WHERE `ID`
IN (SELECT `facultyID` FROM `C_EducationalGroup` WHERE `ID`
IN (SELECT `educationalGroupID` FROM `C_StudyField` WHERE `ID`=@studyFieldID
)));