MySQL Select查询以基于列表值获取记录 [英] MySQL Select query to fetch record base on list values

问题描述

我正在使用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;

这篇关于MySQL Select查询以基于列表值获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！