mysql - 查询不返回所有记录

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

我愿意通过

获取记录

DATE_FORMAT(ap.appDate, '%Y-%m') = '2013-10'

如果某个医生在特定月份没有任何预约，那么医生行返回 0，我该怎么做？

我的愿望实现了

Answer 1

在 Appt 表上尝试左连接

SELECT d.doctorFName,d.doctorLName ,count(ap.appointmentID) totalap,
     GROUP_CONCAT(DISTINCT  s.speciality) specialities,DATE_FORMAT(ap.appDate, '%Y-%m') appMonth
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)
LEFT JOIN tbl_appointment ap on (ap.doctorID = d.doctorID)
INNER JOIN tbl_patients p on p.patientID = ap.patientID 
GROUP BY d.doctorID,appMonth

这将首先显示按 Dr 分组，然后按 appDate 月份分组...如果博士一个月没有申请，它将显示为零我相信这对你有用