MySQL 表中一个字段关联多个code，查询出对应的层级名称

---

使用步骤

1.用到MySQL的FIND_IN_SET()、group_concat()函数，请自行查阅API，这里只解决问题。

业务表存储的数据：

2.两种实现方式sql

第一种：

select
T1.SUBJECT_CODE ,
group_concat(T2.SUBJECT_NAME order by T2.SUBJECT_CODE separator ’ / ') as SUBEJCT_NAME
from
t_itac_subject T1 ,
t_itac_subject T2
where
T1.DATA_STATUS = 1
and T2.DATA_STATUS = 1
and FIND_IN_SET(T2.SUBJECT_CODE, T1.SUBJECT_LEVEL)
group by
T1.SUBJECT_CODE ;

第二种：

select
t.SUBJECT_LEVEL,
t.SUBJECT_CODE,
(
select
replace(GROUP_CONCAT(s1.SUBJECT_NAME),","," / ")
from
t_itac_subject s1
where
s1.SUBJECT_CODE in
(
select substring_index(substring_index(a.SUBJECT_LEVEL,’,’,b.help_topic_id+1),’,’,-1) as code
from
t_itac_subject a
join
mysql.help_topic b
on b.help_topic_id < (length(a.SUBJECT_LEVEL) - length(replace(a.SUBJECT_LEVEL,’,’,’’))+1)
where SUBJECT_CODE = t.SUBJECT_CODE
)
) as identitycategoriesname
from
t_itac_subject t
where
t.SUBJECT_CODE = ‘10200601’
and t.DATA_STATUS = 1;

---

效果图