我有以下三个表"医生"、"专业"one_answers"医生专业":
doctors
-id
-doctor_name
specialities
-id
-speciality_name
doctor_specialities
-id
-doctor_id
-speciality_id
我想把所有医生连同他们的专业名称和专业id一起寄回。一个医生可以有多个专业。
结果集应该看起来像:
id | doctor_name | speciality_id | speciality_name
--------------------------------------------------------------------------------------
1 | John | 5,3 | Speciality1,Speciality2
3 | Tim | 3 | Speciality2
6 | David | NULL | NULL
我尝试了以下查询:
SELECT d.id ,d.doctor_name, s.speciality_name, s.id
AS speciality_id
FROM api_doctors d
LEFT JOIN api_doctor_specialities ds
ON ds.doctor_id = d.id
LEFT JOIN api_specialities s
ON s.id = ds.speciality_id
GROUP BY d.id
但在这种情况下,我得到的是单一专业。
您要查找的是一个名为GROUP_CONCT的MySQL函数,该函数返回串联结果或NULL。默认的分隔符是逗号,因此此查询的结果应该与所需的结果集相匹配。
SELECT d.id, d.doctor_name, GROUP_CONCAT(s.speciality_name) AS speciality_name, GROUP_CONCAT(s.id) AS speciality_id
FROM api_doctors d
LEFT JOIN api_doctor_specialities ds
ON ds.doctor_id = d.id
LEFT JOIN api_specialities s
ON s.id = ds.speciality_id
GROUP BY d.id
SQL Fiddle 上的模式和查询