Mysql内连接和过滤结果

Question

表 1:用户

id name
1  name 1
2  name 2

表2:user_city

user_id city_id
1        1
1        2
2        1
2        2

表3:城市

id name_city
1  HCM
2  Ha Noi
3  DA NAng

我的SQL:

select a.*,c.name_city 
FROM users as a 
INNER JOIN 
user_city as b 
ON b.user_id = a.id 
INNER JOIN city as c 
ON c.id = b.city_id

结果:

id  name  name_city

1  name 1 HCM
2  name 2 Ha Noi
1  name 1 Ha Noi
2  name 2 HCM

我想要的结果是:

结果:

id 名称 name_city

1  name 1 HCM,Ha Noi
2  name 2 HCM,Ha Noi

Answer 1

您可以使用GROUP_CONCAT AND GROUP BY来获取结果

SELECT U.id, U.name, GROUP_CONCAT(c.name_city)
FROM users u
join user_city uc
on u.id = uc.user_id
join city c
on uc.city_id = c.id
group by u.id, u.name