WITH test AS( SELECT '1' dist_id,'0' parent_id,'中国' dist_name, '0' dist_type FROM dual UNION ALL SELECT '2' dist_id,'1' parent_id,'北京' dist_name, '1' dist_type FROM dual UNION ALL SELECT '52' dist_id,'2' parent_id,'北京' dist_name, '2' dist_type FROM dual UNION ALL SELECT '500' dist_id,'52' parent_id,'朝阳区' dist_name, '3' dist_type FROM dual UNION ALL SELECT '501' dist_id,'52' parent_id,'东城区' dist_name, '3' dist_type FROM dual UNION ALL SELECT '3' dist_id,'1' parent_id,'安徽' dist_name, '1' dist_type FROM dual UNION ALL SELECT '36' dist_id,'3' parent_id,'安庆' dist_name, '2' dist_type FROM dual UNION ALL SELECT '399' dist_id,'36' parent_id,'大观区' dist_name, '3' dist_type FROM dual ) , test2 AS( SELECT '1' info_id,'移动沟通100' hall_name,'500' dist_id,'北京朝阳区100号' address FROM dual UNION ALL SELECT '2' info_id,'移动营业厅(朝阳店) ' hall_name,'500' dist_id,'北京朝阳区50号' address FROM dual UNION ALL SELECT '3' info_id,'联通营业厅(东城店)' hall_name,'501' dist_id,'北京东城区9号' address FROM dual UNION ALL SELECT '4' info_id,'电信营业厅(安庆店)' hall_name,'399' dist_id,'安徽省安庆市大观区168' address FROM dual ) SELECT K.INFO_ID, T.*, K.HALL_NAME, K.ADDRESS FROM TEST2 K, (SELECT DIST_ID, DIST_NAME, REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(DIST_NAME, '/'), '[^/]+', 1, 1) 国家, REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(DIST_NAME, '/'), '[^/]+', 1, 2) 省, REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(DIST_NAME, '/'), '[^/]+', 1, 3) 市, REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(DIST_NAME, '/'), '[^/]+', 1, 4) 区, DIST_TYPE FROM TEST START WITH DIST_ID = '1' CONNECT BY PRIOR DIST_ID = PARENT_ID) T WHERE K.DIST_ID = T.DIST_ID(+);
SELECT '1' dist_id,'0' parent_id,'中国' dist_name, '0' dist_type FROM dual UNION ALL
SELECT '2' dist_id,'1' parent_id,'北京' dist_name, '1' dist_type FROM dual UNION ALL
SELECT '52' dist_id,'2' parent_id,'北京' dist_name, '2' dist_type FROM dual UNION ALL
SELECT '500' dist_id,'52' parent_id,'朝阳区' dist_name, '3' dist_type FROM dual UNION ALL
SELECT '501' dist_id,'52' parent_id,'东城区' dist_name, '3' dist_type FROM dual UNION ALL
SELECT '3' dist_id,'1' parent_id,'安徽' dist_name, '1' dist_type FROM dual UNION ALL
SELECT '36' dist_id,'3' parent_id,'安庆' dist_name, '2' dist_type FROM dual UNION ALL
SELECT '399' dist_id,'36' parent_id,'大观区' dist_name, '3' dist_type FROM dual ) ,
test2 AS(
SELECT '1' info_id,'移动沟通100' hall_name,'500' dist_id,'北京朝阳区100号' address FROM dual UNION ALL
SELECT '2' info_id,'移动营业厅(朝阳店) ' hall_name,'500' dist_id,'北京朝阳区50号' address FROM dual UNION ALL
SELECT '3' info_id,'联通营业厅(东城店)' hall_name,'501' dist_id,'北京东城区9号' address FROM dual UNION ALL
SELECT '4' info_id,'电信营业厅(安庆店)' hall_name,'399' dist_id,'安徽省安庆市大观区168' address FROM dual
)
SELECT K.INFO_ID, T.*, K.HALL_NAME, K.ADDRESS
FROM TEST2 K,
(SELECT DIST_ID,
DIST_NAME,
REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(DIST_NAME, '/'),
'[^/]+',
1,
1) 国家,
REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(DIST_NAME, '/'),
'[^/]+',
1,
2) 省,
REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(DIST_NAME, '/'),
'[^/]+',
1,
3) 市,
REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(DIST_NAME, '/'),
'[^/]+',
1,
4) 区, DIST_TYPE
FROM TEST
START WITH DIST_ID = '1'
CONNECT BY PRIOR DIST_ID = PARENT_ID) T
WHERE K.DIST_ID = T.DIST_ID(+);