mysql 多分组拆分重聚合

不同主体有多条记录，每条记录中都有多个类型或不同或重复
现在要求把主体所有的类型汇总为一条

a | 类型1,类型2,类型3
a | 类型2,类型3,类型4
b | 类型2,类型5,类型6,类型8
b | 类型4,类型5,类型6
b | 类型2,类型8,类型9
c | 类型1,类型9
c | 类型3,类型7

⬆⬆⬆ →→→→→→ ⬇⬇⬇

a | 类型1,类型2,类型3,类型4
b | 类型2,类型4,类型5,类型6,类型8,类型9
c | 类型1,类型3,类型7,类型9

select 
t3.col,
group_concat(distinct new_typ) as all_type
from 
	(select 
	t1.col,
	t1.typ,
	t2.seq,
	substring_index(substring_index(t1.typ,',',t2.seq+1),',',-1) new_typ
	from 
		(select 'a' as col ,'类型1,类型2,类型3' as typ union all
		select 'a','类型2,类型3,类型4' union all
		select 'b','类型2,类型5,类型6,类型8' union all
		select 'b','类型4,类型5,类型6' union all
		select 'b','类型2,类型8,类型9' union all
		select 'c','类型1,类型9' union all
		select 'c','类型3,类型7'
		) t1
	left join (select 0 as seq union select 1 union select 2 union select 3 union select 4 union select 5) t2
	on 1=1
-- 	where t2.seq < (length(t1.typ)-length(replace(t1.typ,',',''))+1)
	order by t1.col,t1.typ,t2.seq
	) t3
group by t3.col