with py_sd as 

(

select   id,parentid , level_n from A  where level_n ='1'

union all 

select   prj.id,prj.parentid , level_n+1 from A c inner join  A prj on  c.id =  prj.parentid 

)

insert overwrite table oa.withtest

select id,parentid , level_n from py_sd tf

看你有几个层级关系了，给你举例个四个层级的求法吧：(如果想层级名称没有的就用前一级的层级名称可以用COALESCE函数)

menu_level 表示的是层级ID，tableName表示表名，menu_name表示层级名称

SQL如下：

SELECT

"一级菜单",

"二级菜单",

"三级菜单",

"四级菜单" 

FROM

( SELECT ID, menu_name AS "一级菜单", parent_id FROM tableName WHERE menu_level = 0 )

A LEFT JOIN ( SELECT ID, menu_name AS "二级菜单", parent_id FROM tableName WHERE menu_level = 1 ) b ON A.ID = b.parent_id

LEFT JOIN ( SELECT ID, menu_name AS "三级菜单", parent_id FROM tableName WHERE menu_level = 2 ) C ON b.ID = C.parent_id

LEFT JOIN ( SELECT ID, menu_name AS "四级菜单", parent_id FROM tableName WHERE menu_level = 3 ) d ON C.ID = d.parent_id