SQL查询一个单元格中的多个值 [英] SQL query of multiple values in one cell
问题描述
有一张表格(课程兴趣)在一个单元格中具有所有值.但是这些值只是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.
这篇关于SQL查询一个单元格中的多个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!