MySQL连接两个表，用逗号分隔的ID [英] mysql join two table with comma separated ids

问题描述

我有两个桌子

表1

ID     NAME
1      Person1
2      Person2
3      Person3

表2

ID     GROUP_ID
1      1
2      2,3

以上所有列中的ID均指相同的ID(例如-部门)

The IDs in all the columns above refer to the same ID (Example - a Department)

我的预期输出(通过同时连接两个表)

My Expected output (by joining both the tables)

GROUP_ID     NAME
1            Person1
2,3          Person2,Person3

有没有可以用来实现此目的的查询.非常感谢您的帮助.谢谢.

Is there a query with which i can achieve this. Your help is highly appreciated. Thank you.

推荐答案

您可以在此上使用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

• SQLFiddle演示
• MySQL FIND_IN_SET
• MySQL GROUP_CONCAT()
• SQLFiddle Demo
• MySQL FIND_IN_SET
• MySQL GROUP_CONCAT()

输出

╔══════════╦═════════════════╗
║ GROUP_ID ║      NAME       ║
╠══════════╬═════════════════╣
║ 1        ║ Person1         ║
║ 2,3      ║ Person2,Person3 ║
╚══════════╩═════════════════╝

作为一个旁注，此查询可能无法按预期有效地执行.请不要保存用逗号分隔的值，以正确地标准化表格.

As a sidenote, this query might not perform efficiently as expected. Please do normalize your table properly by not saving values separated by a comma.

更新

GROUP_ID几乎令人困惑.是PersonIDList吗?无论如何，这是我建议的架构设计:

GROUP_ID is pretty much confusing. Isn't it PersonIDList? Anyway, here's my suggested schema design:

PERSON表

• 人名(PK)
• PersonName
• 其他列..
• PersonID (PK)
• PersonName
• other columns..

组表

• GroupID(PK)
• GroupName
• 其他列.
• GroupID (PK)
• GroupName
• other columns..

PERSON_GROUP表

• PersonID(FK)(同时带有GroupID列的PK)
• GroupID(FK)

这篇关于MySQL连接两个表，用逗号分隔的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！