hive实现id1_id2,id3_id4对应的实际值对应关系；370000_450000,440000_130000,450000_440000 ---＞ 陕西_拉萨,郑州_四川,拉萨_郑州

 

        同事问了这么个问题：

370000_450000,440000_130000,450000_440000 这么一组城市id怎么转换成实际的城市名称对应关系，有一个维表会维护对应的关系。

记录下这个怎么写：

select  concat_ws(',',collect_list(concat(t2.col2,"_",t3.col2))) as result
    	,t1.col1
    	,concat_ws(',',collect_list(t1.col0)) as tt
from   	(
			select  split(col0,'_')[0] as v1
			      	,split(col0,'_')[1] as v2
			      	,col1
			      	,col0
			from   	(
			    		select   "370000_450000,440000_130000,450000_440000" as col1
			  		) t100
	      lateral view explode(split(col1,",")) t21 as col0
    	) t1 left
join   (
			select  370000 as col1,'陕西' as col2
			union all
			select  130000 as col1,'四川' as col2
			union all
			select  450000 as col1,"拉萨" as col2
			union all
			select  440000 as col1,"郑州" as col2
    	) t2
on     	t1.v1 = t2.col1 left
join   	(
			select  370000 as col1,'陕西' as col2
			union all
			select  130000 as col1,'四川' as col2
			union all
			select  450000 as col1,"拉萨" as col2
			union all
			select  440000 as col1,"郑州" as col2
    	) t3
on     	t1.v2 = t3.col1
group by t1.col1
;