拼接/切割筛选列出MySQL表中2个字段值的数字ID部分

    现有一张数据表，数据如下图：

    需求是将上图中数据整理成下图格式：

    可以用下面的SQL实现：

        select distinct substring_index(substring_index(a.roleids,'|',b.help_topic_id+1),'|',-1) 'roleid'

from

    (select (CASE when roles = "" then received when received = "" then roles else concat(roles,"|",received) END) 'roleids' from Database_Name.Table_Name) a

join

        mysql.help_topic b

on

        b.help_topic_id < (length(a.roleids) - length(replace(a.roleids,'|',''))+1) order by roleid;