问题与MySQL子查询

Question

我很困惑如何进行一个SQL查询，它将显示学生的详细信息谁正在采取由特定讲师教的主题。

有5个表是我的db。

主题（subjectCode，subjectName，creditHour，studyMode）

学生（stdID, stdName`）

讲师（lecID, lecName`）

讲师学科（lec_subID，lec_userID，subjectID）

学生主题（std_subID ，student_userID，subjectCode）

Answer 1

select Sturent.stdName as StudentName, 
    Subject.subjectName as SubjectName, 
    Lecturer.lecName as LecturerName, 
    Subject.creditHour as CreditHour, 
    Subject.studyMode as StudyMode from Student 
    join Student-subject on student.stdID= Student-subject.student_userID 
    join Subject on Student-subject.subjectCode = subject.subjectCode 
    join Lecturer-subject on Lecturer-subject.subjectID = subject.subjectCode 
    join Lecturer on Lecturer-subject.lec_userID = lectrer.lecID 

或

select Sturent.stdName as StudentName, 
    Subject.subjectName as SubjectName, 
    Lecturer.lecName as LecturerName, 
    Subject.creditHour as CreditHour, 
    Subject.studyMode as StudyMode from Student, Student-subject, 
Subject, Lecturer-subject, Lecturer 
where student.stdID= Student-subject.student_userID 
    and Student-subject.subjectCode = subject.subjectCode 
    and Lecturer-subject.subjectID = subject.subjectCode 
    and Lecturer-subject.lec_userID = lectrer.lecID 

Answer 2

试试这个它会工作：

使用Inner Join

Select t1.`stdId`,t1.`stdName`,t2.`std_subID`,t3.`subjectName`,t3.`creditHour`,t3.`studyMode`,t4.`lec_subID`,t5.`lecName` from Student t1 
JOIN Student-subject t2 ON t2.`student_userId`=t1.`stdID` 
JOIN Subject t3 ON t3.`subjectCode`=t2.`subjectCode` 
JOIN Lecturer-subject t4 ON t4.`subjectID`=t2.`std_subID` 
JOIN Lecture t5 ON t5.`lecID`=t4.`lec_userID` 

Answer 3

你需要应用join和WHERE子句来获取你想要的数据。

SELECT DISTINCT 
    Sturent.stdName as StudentName, 
    Subject.subjectName as SubjectName, 
    Lecturer.lecName as LecturerName, 
    Subject.creditHour, 
    Subject.studyMode 
FROM 
    Student JOIN Student-subject 
    ON student.stdID= Student-subject.student_userID 
    JOIN Subject 
    ON Student-subject.subjectCode = subject.subjectCode 
    JOIN Lecturer-subject 
    ON Lecturer-subject.subjectID = subject.subjectCode 
    JOIN Lecturer 
    ON Lecturer-subject.lec_userID = lectrer.lecID 
WHERE 
    Lecturer.lecName = 'some lecturer name'