mysql逗号分开与合并

一、转换之前

 

 

 二、转换之后

 

 

 三、代码实现（只需要换表名（t_media_copy2）和字段（MEDIAINFO））

with aa as(
SELECT
    a.id,
    substring_index(
        substring_index(
            a.MEDIAINFO,
            ',',
            b.help_topic_id + 1
        ),
        ',' ,- 1
    ) AS MEDIAINFO
FROM
    t_media_copy2 a
JOIN mysql.help_topic b ON b.help_topic_id < (
    length(a.MEDIAINFO) - length(
        REPLACE (a.MEDIAINFO, ',', '')
    ) + 1
))
select aa.id, aa.mediaInfo, c.name from aa 
left join t_column c on c.id = aa.MEDIAINFO

四、合并连表的数据

 

 

with aa as(
SELECT
    a.id,
    substring_index(
        substring_index(
            a.MEDIAINFO,
            ',',
            b.help_topic_id + 1
        ),
        ',' ,- 1
    ) AS MEDIAINFO
FROM
    t_media_copy2 a
JOIN mysql.help_topic b ON b.help_topic_id < (
    length(a.MEDIAINFO) - length(
        REPLACE (a.MEDIAINFO, ',', '')
    ) + 1
))
select aa.id, group_concat(distinct c.name SEPARATOR  '/') name 
from aa 
left join t_column c on c.id = aa.MEDIAINFO
group by aa.id