mysql 一个字段多个id逗号分隔关联查询_多个id放在一个字段查询走索引

mysql 一个字段多个id逗号分隔关联查询

关键点是：FIND_IN_SET、GROUP_CONCAT、GROUP BY

-- mysql 查询逗号分隔的多个id
select t1.id,t1.chapter_id,
	t2.id,t2.name as bookVersionName,
	GROUP_CONCAT(t3.`name`) as chapterName,
	t4.id,t4.name as volumeName 
from co_course_pj as t1,
	co_book_version as t2,
	co_chapter as t3,
	co_volume as t4
where t1.`status` != "0" 
	and t2.id = t1.book_version_id  
	and FIND_IN_SET(t3.id, t1.chapter_id) 
	and t4.id = t1.volume_id 
GROUP BY t1.id;
 
 
-- 科目学段
SELECT 
    t1.id, t1.`name`, t1.stage_ids, GROUP_CONCAT(t2.`name`) AS stageNames
FROM co_subject as t1
left JOIN co_school_stage as t2 ON FIND_IN_SET(t2.id,t1.stage_ids) and t2.`status` != "0"
GROUP BY t1.id;