©2020-2024 ioDraw All rights reserved
<>结果样式
-- sql原q SELECT SYS_CONNECT_BY_PATH(T.AREANAME, '-->') AS RESULT, T.AREACODE, T
.AREANAME, T.PARENTCODE, LEVEL FROM TAREAINFOTREE T START WITH T.AREACODE =
'0001' CONNECT BY PRIOR T.AREACODE = T.PARENTCODE ORDER BY LEVEL, T.AREACODE;
<>hive 准备
-- -- -- 测试环境准备 -- -- -- -- 创建测试表 drop table if exists pub.test; create table
pub.test( areaname string,areacode string,parentcode string ) row format
delimitedfields terminated by ','; -- 创建测试数据 -- values 不好使 why！ INSERT into
TABLE pub.test SELECT t.* from(SELECT 0 ) a lateral view inline(array( struct(
'a','0001' ,'0'),struct('aa','00010001','0001'), struct('ab','00010002','0001'),
struct('ac','00010003','0001'), struct('b','0002','0'),struct('ba','00020001',
'0002'), struct('aaa','000100010001','00010001'), struct('aaaa',
'0001000100010001','000100010001'), struct('aaaaa','00010001000100010001',
'0001000100010001') )) t as areaname,areacode,parentcode;
<>执行hql脚本
-- -- -- 查询插入结果 -- -- -- -- 创建临时表 drop table if exists pub.test_temp; create
table pub.test_temp( level1 struct<areaname:string,areacode:string,
parentcode:string>, level2 struct<areaname:string,areacode:string,
parentcode:string>, level3 struct<areaname:string,areacode:string,
parentcode:string>, level4 struct<areaname:string,areacode:string,
parentcode:string>, level5 struct<areaname:string,areacode:string,
parentcode:string> ); -- 1 INSERT overwrite table pub.test_temp SELECT
named_struct('areaname',t.areaname,'areacode',t.areacode,'parentcode',t.
parentcode), named_struct('areaname','','areacode','','parentcode',''),
named_struct('areaname','','areacode','','parentcode',''), named_struct(
'areaname','','areacode','','parentcode',''),named_struct('areaname','',
'areacode','','parentcode','') from pub.test t where LENGTH(t.areacode)<=4; -- 2
INSERT into table pub.test_temp SELECT a.level1, named_struct('areaname',t.
areaname,'areacode',t.areacode,'parentcode',t.parentcode), named_struct(
'areaname','','areacode','','parentcode',''), named_struct('areaname','',
'areacode','','parentcode',''),named_struct('areaname','','areacode','',
'parentcode','') from pub.test t join pub.test_temp a on t.parentcode=a.level1.
areacode; -- 3 INSERT into table pub.test_temp SELECT a.level1,a.level2,
named_struct('areaname',t.areaname,'areacode',t.areacode,'parentcode',t.
parentcode), named_struct('areaname','','areacode','','parentcode',''),
named_struct('areaname','','areacode','','parentcode','') from pub.test t join
pub.test_temp a on t.parentcode=a.level2.areacode; -- 4 INSERT into table pub.
test_tempSELECT a.level1,a.level2,a.level3, named_struct('areaname',t.areaname,
'areacode',t.areacode,'parentcode',t.parentcode), named_struct('areaname','',
'areacode','','parentcode','') from pub.test t join pub.test_temp a on t.
parentcode=a.level3.areacode; -- 5 INSERT into table pub.test_temp SELECT a.
level1,a.level2,a.level3,a.level4, named_struct('areaname',t.areaname,'areacode'
,t.areacode,'parentcode',t.parentcode) from pub.test t join pub.test_temp a on t
.parentcode=a.level4.areacode; -- 目标查询 SELECT * FROM( SELECT CONCAT('-->',t.
level1.areaname,if(LENGTH(t.level2.areacode)>0, CONCAT('-->',t.level2.areaname,
if(LENGTH(t.level3.areacode)>0, CONCAT('-->',t.level3.areaname,if(LENGTH(t.
level4.areacode)>0, CONCAT('-->',t.level4.areaname,if(LENGTH(t.level5.areacode)>
0, CONCAT('-->',t.level5.areaname),'') ),'') ),'') ),'') ) as result, case WHEN
length(t.level5.areacode)>0 THEN t.level5.areacode WHEN length(t.level4.areacode
)>0 THEN t.level4.areacode WHEN length(t.level3.areacode)>0 THEN t.level3.
areacodeWHEN length(t.level2.areacode)>0 THEN t.level2.areacode WHEN length(t.
level1.areacode)>0 THEN t.level1.areacode END as areacode, case WHEN length(t.
level5.areaname)>0 THEN t.level5.areaname WHEN length(t.level4.areaname)>0 THEN
t.level4.areaname WHEN length(t.level3.areaname)>0 THEN t.level3.areaname WHEN
length(t.level2.areaname)>0 THEN t.level2.areaname WHEN length(t.level1.areaname
)>0 THEN t.level1.areaname END as areaname, case WHEN length(t.level5.parentcode
)>0 THEN t.level5.parentcode WHEN length(t.level4.parentcode)>0 THEN t.level4.
parentcodeWHEN length(t.level3.parentcode)>0 THEN t.level3.parentcode WHEN
length(t.level2.parentcode)>0 THEN t.level2.parentcode WHEN length(t.level1.
parentcode)>0 THEN t.level1.parentcode END as parentcode, case WHEN length(t.
level5.parentcode)>0 THEN 5 WHEN length(t.level4.parentcode)>0 THEN 4 WHEN
length(t.level3.parentcode)>0 THEN 3 WHEN length(t.level2.parentcode)>0 THEN 2
WHEN length(t.level1.parentcode)>0 THEN 1 END as level from pub.test_temp t )t
ORDER BY t.level;
<>检查
--检验流程结果 select * from pub.test; SELECT * from pub.test_temp t;