转行
DROP TABLE IF EXISTS `temp_datalist`; CREATE TEMPORARY TABLE temp_datalist select distinct a.id, substring_index(substring_index(a.desc, ',', b.help_topic_id + 1), ',',-1) cd from table a join mysql.help_topic as b on b.help_topic_id < (char_length(a.desc) - char_length(replace(a.desc, ',', ''))+ 1) where type = 'A' and sub_type = 'B';
转列(此例使用【|】进行分割)
select
a.id,
a.desc,
substring_index(substring_index(replace(a.desc,'补充|',''),'|',1),'|',-1) a,
substring_index(substring_index(replace(a.desc,'补充|',''),'|',2),'|',-1) b,
substring_index(substring_index(replace(a.desc,'补充|',''),'|',3),'|',-1) c,
substring_index(substring_index(replace(a.desc,'补充|',''),'|',4),'|',-1) d
from
table a
JSON化
JSON_ARRAYAGG(JSON_OBJECT('name', ifnull(c.code,''))) jsontxt