mysql FIND_IN_SET函数,根据id(逗号隔开)关联另一个表查询name(结果逗号隔开)

a表:

b表:

 

 需求：a表role关联B表查询出角色名称并用逗号隔开

SELECT
	a.id,
	a.NAME,
	GROUP_CONCAT(b.NAME) AS role
FROM
	USER a
INNER JOIN role b ON FIND_IN_SET(b.id, a.role)
GROUP BY
	a.id

查询结果如下：