MySQL Select查询以基于列表值获取记录
问题描述：
我正在使用MySQL. 我有3张桌子，如下所示.
I am using MySQL. I have 3 Table as below.
表:subject_Master
Table : subject_Master
--------------------------
subjectId | subjectShortName
----------------------------------
1 | English
2 | French
3 | German
4 | Latin
----------------------------------
表:class_Master
Table : class_Master
-----------------------------------
classId | className
----------------------------------
1 | Rose
2 | Dasy
3 | Lily
表:subjectAllocation
Table : 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
答
使用尝试一下:
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;