【mysql】 将一列用符号拼接成一行&&拆分一个逗号拼接的字符串为多行

 

表t_work_order_type 的字段task_id为用逗号拼接的字符串

1.拆分一个逗号拼接的字符串：用SUBSTRING_INDEX

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(a.path,',',b.help_topic_id + 1),',',-1) task_id FROM
        (SELECT  task_id as path from t_work_order_type) a JOIN mysql.help_topic b
         ON b.help_topic_id < (LENGTH(a.path) - LENGTH(REPLACE(a.path,',','')) + 1)

2.将一列用符号拼接成一行,用group_concat(t.id order by t.overhaultype  separator ',') 

SELECT  group_concat(t.id order by t.overhaultype  separator ',') as id,t.other
		from
		(
				 。。。

		) t GROUP BY t.other