我下面的查询不起作用,因为我基本上同意,我愿意以逗号分隔的方式获取每个医生及其专长的所有记录,还可以获取每个月的医生约会,但查询重复逗号分隔的专业。
select d.doctorFName,d.doctorLName ,count(ap.appointmentID) totalap,
GROUP_CONCAT(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
我的这对每个医生及其专业都很好用逗号分隔
select d.doctorFName,d.doctorLName ,
GROUP_CONCAT(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)
GROUP BY d.doctorID
但是当我添加联接以重复其特长时,下面是我的数据库,希望您能通过query理解数据。我在哪里做错了?以及我该如何使用
DATE_FORMAT(ap.appDate, '%Y-%m') = '2013-10'
以上查询的原因是itrs出现错误
DISTINCT
在group_concat中尝试,一对多的内部联接将生成笛卡尔乘积,因此您将获得重复的值
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句