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

最新推荐文章于 2024-08-14 16:58:32 发布

kenhins

最新推荐文章于 2024-08-14 16:58:32 发布

阅读量4.2k

点赞数 1

分类专栏： SQL mysql

本文链接：https://blog.csdn.net/kenhins/article/details/106047220

版权

mysql 同时被 2 个专栏收录

35 篇文章 0 订阅

订阅专栏

SQL

33 篇文章 0 订阅

订阅专栏

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;