mysql list类型转换_mysql逗号分隔List字段转多行

具体的逻辑我还没整明白，先记上再说，亲测可用。

1、原表数据

select a1.id,a1.job_depends

from job_version_history a1

where a1.id in (1655,1656);

id    job_depends

1655    353,3,532

1656    484,5,567

2、一行转多列

select a1.id,a1.job_depends,substring_index(substring_index(a1.job_depends,',',a2.id+0),',',-1) job_depends_id

from job_version_history a1

join job a2 on a2.id < (length(a1.job_depends) - length(replace(a1.job_depends,',',''))+2)

where a1.id in (1655,1656);

id    job_depends    job_depends_id

1655    353,3,532    353

1655    353,3,532    3

1655    353,3,532    532

1656    484,5,567    484

1656    484,5,567    5

1656    484,5,567    567

说明：这个job里的id，是一个自增的列；它在这里的作用就是提供一个足够大的序列。

3、另一个转成多行后关联到注释说明后又concat成list的示例

SELECT a11.job_id,

a11.job_name,

a11.job_owner_name,

a11.last_release_time,

a11.project_name,

a11.job_type,

group_concat(a11.job_depends_split_id) job_depends_id,

group_concat(a22.NAME) job_depends_name

FROM

(SELECT a1.job_id,

a1.job_name,

a1.job_owner_name,

a1.job_release_time last_release_time,

a2.`name` project_name,

a1.job_type,

a1.job_depends,

substring_index(substring_index(a1.job_depends,',',a9.id+0),',',-1) job_depends_split_id

FROM job_version_history a1

LEFT JOIN project a2 ON a1.project_id=a2.id

JOIN job a9 ON a9.id < (length(a1.job_depends) - length(replace(a1.job_depends,',',''))+2)

WHERE a1.job_end_version = 2147483647) a11

LEFT JOIN job a22 ON a11.job_depends_split_id=a22.id

GROUP BY a11.job_id,

a11.job_name,

a11.job_owner_name,

a11.last_release_time,

a11.project_name,

a11.job_type;

说明：合成以逗号分隔的多行转一行关键字group_concat。