mysql - 查询不适用于 DATE_FORMAT

Question

select  d.doctorFName,d.doctorLName ,count(ap.appointmentID) totalap,
GROUP_CONCAT(DISTINCT  s.speciality) specialities
FROM  tbl_doctors d
INNER JOIN  tbl_doctor_speciality  ds ON (d.doctorID = ds.doctorID)
INNER JOIN tbl_speciality  s ON (s.spID = ds.spID)
Inner join tbl_appointment ap on (ap.doctorID = d.doctorID)
Inner join tbl_patients p on p.patientID = ap.patientID 
GROUP BY d.doctorID
where d.status = 1 and  DATE_FORMAT(ap.appDate, '%Y-%m') = '2013-10'

我的下面的查询在我做错的地方后给了我错误，请帮助我？

where d.status = 1 and  DATE_FORMAT(ap.appDate, '%Y-%m') = '2013-10'

错误

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where d.status = 1 and ds.spID and DATE_FORMAT(ap.appDate, '%Y-%m') = '2013-10' at line 9

Answer 1

where 子句位于错误的位置。它位于group by之前:

select d.doctorFName,d.doctorLName ,count(ap.appointmentID) totalap,
       GROUP_CONCAT(DISTINCT  s.speciality) specialities
FROM  tbl_doctors d
INNER JOIN  tbl_doctor_speciality  ds ON (d.doctorID = ds.doctorID)
INNER JOIN tbl_speciality  s ON (s.spID = ds.spID)
Inner join tbl_appointment ap on (ap.doctorID = d.doctorID)
Inner join tbl_patients p on p.patientID = ap.patientID 
where d.status = 1 and  DATE_FORMAT(ap.appDate, '%Y-%m') = '2013-10'
GROUP BY d.doctorID;