如下图
select
sd.*,GROUP_CONCAT(su.nickname) as coach_name,GROUP_CONCAT(su.id) as coach_id,sc.canteen_name
from sys_department sd
left join sys_department_coach sdco on sdco.department_id=sd.id
left join sys_user su on su.id=sdco.coach_id
left join sys_canteen sc on sc.id=sd.parent_id
GROUP BY sd.id
GROUP_CONCAT(需要合并显示的数据) as 合并后显示的表名(可自定义)
GROUP BY 这个一定要写,不然会出现下面的情况