一个单元格中多个值的 SQL 查询

2021-11-20 00:00:00 sql mysql

有一个表格(课程兴趣)，其中包含一个单元格中的所有值.但这些值只是 id，我想将它们与另一个表(课程)连接起来，这样我就可以知道它们的名字.

There is a table(Course Interests) which has all the values in one cell. But those values are just ids and I want to join them with another table(Course) so I can know their names.

课程兴趣:

MemberID          MemberName              CoursesInterested
--------------    ---------------------   --------------
1                  Al                     1,4,5,6
2                  A2                     3,5,6

课程表:

CourseId          Course
--------------    ---------------------
1                 MBA 
2                 Languages
3                 English
4                 French
5                 Fashion
6                 IT

期望的输出:

MemberID          MemberName              CoursesInterested
--------------    ---------------------   --------------
1                  Al                     MBA,French,Fashion,IT
2                  A2                     English,Fashion,IT

我想在 MySql 中做一个 SQL 查询，它可以帮助我提取所需的输出.我知道如何以相反的方式进行操作(将值连接到一个单元格)，但我一直在努力寻找一种方法来分离 ID 并将交叉连接到另一个表中.

I would like to do a SQL query in MySql that can help me to extract the desired output. I know how to do it in the opposite way(join values to one cell), but I've struggling on seek a way to separate the ids and do a cross-join into another table.

我将感谢社区的任何帮助.谢谢

I'll appreciate any help from the community. Thanks

推荐答案

使用 FIND_IN_SET 在逗号分隔的列表中搜索内容.

Use FIND_IN_SET to search for something in a comma-delimited list.

SELECT i.MemberID, i.MemberName, GROUP_CONCAT(c.Course) AS CoursesInterested
FROM CourseInterests AS i
JOIN Course AS c ON FIND_IN_SET(c.CourseId, i.CoursesInterested)

但是，最好创建一个关系表而不是将课程存储在单个列中.这种类型的连接无法使用索引进行优化，因此对于大表来说成本会很高.

However, it would be better to create a relation table instead of storing the courses in a single column. This type of join cannot be optimized using an index, so it will be expensive for a large table.

相关文章