MySQL Select查询以基于列表值获取记录

Question

我正在使用MySQL。我有3表，如下所示。

表：subject_Master

--------------------------
subjectId   | subjectShortName
----------------------------------
1           |   English
2           |   French
3           |   German
4           |   Latin
----------------------------------

表格：class_Master

-----------------------------------
classId     | className
----------------------------------
1           |   Rose
2           |   Dasy
3           |   Lily

表：subjectAllocation

------------------------------------------
allocationId    |   classId |   subjectId
-------------------------------------------
1               |   1       |   1,2
2               |   2       |   2,3,4
3               |   3       |   1,2,3,4

我如何获得如下的SQL结果，想获取subjectAllocation行中每个subjectId的SubjectName

ClassName   |   SubjectName
-------------------------------------------
Rose        |   English,French
Dasy        |   French,German,Latin
Lily        |   English,French,German,Latin

Answer 1

使用 FIND_IN_SET（） 函数：

试试这个：

SELECT A.allocationId, 
       B.className, 
       GROUP_CONCAT(C.subjectShortName) AS subjectName
FROM subjectAllocation A
INNER JOIN class_Master B ON A.classId = B.classId 
INNER JOIN subject_Master C ON FIND_IN_SET(C.subjectId, A.subjectId) 
GROUP BY A.allocationId;