MySQL - 连接来自同一个表的查询

Question

期望的输出 - https://www.dropbox.com/s/1mnrabcefvezt89/test11.png?dl=0

我正在尝试在 attend_Date 上连接同一个表中的这 4 个不同查询，但我不知道该怎么做。最终输出为Attend_Date、P、A、MC(共4列)

SELECT Attendance.Attend_Date
FROM Student, Attendance
LEFT OUTER JOIN (
SELECT Attendance.Attend_Date, COUNT(Attendance.AttendDet_Type) as P
FROM Student, Attendance 
WHERE Student.Stud_ID = Attendance.Stud_ID 
AND Student.Stud_Class = '1A1' 
AND Attendance.Attend_Date BETWEEN '2014-01-01' AND '2014-12-01'  
AND Attendance.AttendDet_Type = 'P'
GROUP BY Attendance.Attend_Date
) ON Attendance.Attend_Date
WHERE Student.Stud_ID = Attendance.Stud_ID 
AND Student.Stud_Class = '1A1' 
AND Attendance.Attend_Date BETWEEN '2014-01-01' AND '2014-12-01'  
GROUP BY Attendance.Attend_Date

谁能帮帮我吗？非常感谢!!

Answer 1

尝试在 from 语句中添加同一个表两次，然后将它们设置为变量名并正确重命名所有内容。例如，

FROM Student S1, Attendance A1
WHERE S1.Stud_ID = A1.Stud_ID 

还有另一个

FROM Student S2, Attendance A2
WHERE S2.Stud_ID = A2.Stud_ID 

问题是连接上的外部表和内部表存在冲突，可能会导致您的问题