我有三个表,分别命名为location
、Hospital
和hospital location
。这是两个表的字段和数据
表格:位置
id | location_name
1 | location1
2 | location2
表:医院
id | hospital_name
1 | Hospital1
2 | Hospital2
表:医院位置
id | hospital_id | location_id
1 | 1 | 1
2 | 1 | 2
我需要在mysql中创建一个查询来显示医院表中的所有数据。location_name列有多个值,用逗号分隔。
id | hospital_name | location_name
1 | Hospital1 | location1, location2
您需要的是GROUP_CONCAT-mysql函数http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat
SELECT h.hospital_name, GROUP_CONCAT(l.location_name) as location_name
FROM hospital h
LEFT JOIN hospital_location hl ON hl.hospital_id = h.id
LEFT JOIN location.l ON hl.location_id = l.id
GROUP BY h.id