查询不使用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;

Answer 2

你需要交换这两行

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