mysql 将某列以逗号分割的值转列表

1. 以 TbSelfStudyRoom 表为例子

• 将 fkCoach 的值变为列表

  
  例子.png

2. 方法

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(a.fkCoach,',',b.help_topic_id+1),',',-1) 
FROM
`TbSelfStudyRoom`  a
JOIN
mysql.help_topic b
ON b.help_topic_id < (LENGTH(a.fkCoach) - LENGTH(REPLACE(a.fkCoach,',',''))+1)
ORDER BY a.fkCoach;

3. 效果

结果.png

4. 只取最终结果并去重

SELECT DISTINCT(f.fkCoach) FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(a.fkCoach,',',b.help_topic_id+1),',',-1) AS fkCoach
FROM
`TbSelfStudyRoom`  a
JOIN
mysql.help_topic b
ON b.help_topic_id < (LENGTH(a.fkCoach) - LENGTH(REPLACE(a.fkCoach,',',''))+1)
ORDER BY a.fkCoach) f;

去重2.png