mysql实现hive里面collect_set函数功能

在做数据迁移的过程中,hive迁移到mysql,hql使用的collect_set函数在mysql中跑会报错,原因是mysql并不支持collect_set函数

在mysql中执行报错如下:
SQL 错误 [1305] [42000]: (conn=273190) FUNCTION collect_set does not exist


hive中collect_set函数的功能示例:
--按id分组,对sex去重后再列转行
select id,concat_ws(',',collect_set(item1)) as item1,concat_ws(',',collect_set(item2)) as item2 from 
(
select '1' as id, '木头' as item1, '未知' as item2
union all select '1' as id, '铅笔' as item1 , '未知' as item2
union all select '2' as id, '未知' as item1 , '风扇' as item2
union all select '2' as id, '未知' as item1 , '灯' as item2
) t1
group by id
;
+-----+------------+----------+
| id  |   item1    |  item2   |
+-----+------------+----------+
| 1   | 木头,铅笔  | 未知     |
| 2   | 未知       | 风扇,灯  |
+-----+------------+----------+

mysql中collect_set函数的功能示例:
select tt1.id,tt1.item1,tt2.item2 from
(
	select id,group_concat(item1) as item1 from 
	(
		select id,item1 from 
		(
		select '1' as id, '木头' as item1, '未知' as item2
		union all select '1' as id, '铅笔' as item1 , '未知' as item2
		union all select '2' as id, '未知' as item1 , '风扇' as item2
		union all select '2' as id, '未知' as item1 , '灯' as item2
		) t1
		group by id,item1
	) t2
	group by id
) tt1

join 
(
	select id,group_concat(item2) as item2 from 
	(
		select id,item2 from 
		(
		select '1' as id, '木头' as item1, '未知' as item2
		union all select '1' as id, '铅笔' as item1 , '未知' as item2
		union all select '2' as id, '未知' as item1 , '风扇' as item2
		union all select '2' as id, '未知' as item1 , '灯' as item2
		) t1
		group by id,item2
	) t2
	group by id
) tt2
on tt1.id=tt2.id
;
id	item1	item2
1	木头,铅笔	未知
2	未知	        灯,风扇