您可以使用FIND_IN_SET()
与GROUP_CONCAT()
此,
SELECT b.Group_ID, GROUP_CONCAT(a.name) name
FROM Table2 b
INNER JOIN Table1 a
ON FIND_IN_SET(a.ID, b.Group_ID) > 0
GROUP BY b.Group_ID
╔══════════╦═════════════════╗
║ GROUP_ID ║ NAME ║
╠══════════╬═════════════════╣
║ 1 ║ Person1 ║
║ 2,3 ║ Person2,Person3 ║
╚══════════╩═════════════════╝
附带说明,此查询可能无法按预期有效地执行。请不要保存用逗号分隔的值,以正确规范化您的表格。
GROUP_ID
非常令人困惑。是不是PersonIDList
?无论如何,这是我建议的架构设计:
PERSON Table
PersonID (PK) PersonName other columns.. GROUP Table
GroupID (PK) GroupName other columns.. PERSON_GROUP Table
PersonID (FK) (at the same time PK with column GroupID) GroupID (FK)