WITH dep AS( SELECT 6 DEPID,'项目测试部' DEPNAME,2 UPPERDEPID FROM dual UNION ALL SELECT 0, '总经办', null FROM dual UNION ALL SELECT 1, '开发部', 0 FROM dual UNION ALL SELECT 2, '测试部', 0 FROM dual UNION ALL SELECT 3, 'Sever开发部', 1 FROM dual UNION ALL SELECT 4, 'Client开发部', 1 FROM dual UNION ALL SELECT 5, 'TA测试部', 2 FROM dual ) SELECT RPAD(' ', 2 * (LEVEL - 1), '-') || DEPNAME "DEPNAME", CONNECT_BY_ROOT DEPNAME "ROOT", CONNECT_BY_ISLEAF "ISLEAF", LEVEL, SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH" FROM DEP START WITH UPPERDEPID IS NULL CONNECT BY PRIOR DEPID = UPPERDEPID; 先给你一个我之前写的参考,一会我帮你写这个sql
第一个北京应该是在省里面。因为TYPE=1;WITH T AS (SELECT 1 DIST_ID, 0 PARENT_ID, 'china' DIST_NAME, 0 DIST_TYPE FROM DUAL UNION ALL SELECT 2 DIST_ID, 1 PARENT_ID, 'beijing' DIST_NAME, 1 DIST_TYPE FROM DUAL UNION ALL SELECT 52 DIST_ID, 2 PARENT_ID, 'beijing' DIST_NAME, 2 DIST_TYPE FROM DUAL UNION ALL SELECT 500 DIST_ID, 52 PARENT_ID, 'chaoyang' DIST_NAME, 3 DIST_TYPE FROM DUAL UNION ALL SELECT 501 DIST_ID, 52 PARENT_ID, 'dongcheng' DIST_NAME, 3 DIST_TYPE FROM DUAL UNION ALL SELECT 3 DIST_ID, 1 PARENT_ID, 'anhui' DIST_NAME, 3 DIST_TYPE FROM DUAL UNION ALL SELECT 36 DIST_ID, 3 PARENT_ID, 'anqing' DIST_NAME, 3 DIST_TYPE FROM DUAL UNION ALL SELECT 399 DIST_ID, 36 PARENT_ID, 'daguan' DIST_NAME, 3 DIST_TYPE FROM DUAL) SELECT T.DIST_ID, T.PARENT_ID, DECODE(T.DIST_TYPE, 1, T.DIST_NAME, NULL) PROVANCE, DECODE(T.DIST_TYPE, 2, T.DIST_NAME, NULL) CITY, DECODE(T.DIST_TYPE, 3, T.DIST_NAME, NULL) DISTRICT, T.DIST_TYPE FROM T START WITH DIST_ID = 2 CONNECT BY T.PARENT_ID = PRIOR T.DIST_ID;
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 ) 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;
SELECT 6 DEPID,'项目测试部' DEPNAME,2 UPPERDEPID FROM dual UNION ALL
SELECT 0, '总经办', null FROM dual UNION ALL
SELECT 1, '开发部', 0 FROM dual UNION ALL
SELECT 2, '测试部', 0 FROM dual UNION ALL
SELECT 3, 'Sever开发部', 1 FROM dual UNION ALL
SELECT 4, 'Client开发部', 1 FROM dual UNION ALL
SELECT 5, 'TA测试部', 2 FROM dual
)
SELECT RPAD(' ', 2 * (LEVEL - 1), '-') || DEPNAME "DEPNAME",
CONNECT_BY_ROOT DEPNAME "ROOT",
CONNECT_BY_ISLEAF "ISLEAF",
LEVEL,
SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH"
FROM DEP
START WITH UPPERDEPID IS NULL
CONNECT BY PRIOR DEPID = UPPERDEPID;
先给你一个我之前写的参考,一会我帮你写这个sql
(SELECT 1 DIST_ID, 0 PARENT_ID, 'china' DIST_NAME, 0 DIST_TYPE
FROM DUAL
UNION ALL
SELECT 2 DIST_ID, 1 PARENT_ID, 'beijing' DIST_NAME, 1 DIST_TYPE
FROM DUAL
UNION ALL
SELECT 52 DIST_ID, 2 PARENT_ID, 'beijing' DIST_NAME, 2 DIST_TYPE
FROM DUAL
UNION ALL
SELECT 500 DIST_ID, 52 PARENT_ID, 'chaoyang' DIST_NAME, 3 DIST_TYPE
FROM DUAL
UNION ALL
SELECT 501 DIST_ID, 52 PARENT_ID, 'dongcheng' DIST_NAME, 3 DIST_TYPE
FROM DUAL
UNION ALL
SELECT 3 DIST_ID, 1 PARENT_ID, 'anhui' DIST_NAME, 3 DIST_TYPE
FROM DUAL
UNION ALL
SELECT 36 DIST_ID, 3 PARENT_ID, 'anqing' DIST_NAME, 3 DIST_TYPE
FROM DUAL
UNION ALL
SELECT 399 DIST_ID, 36 PARENT_ID, 'daguan' DIST_NAME, 3 DIST_TYPE
FROM DUAL)
SELECT T.DIST_ID,
T.PARENT_ID,
DECODE(T.DIST_TYPE, 1, T.DIST_NAME, NULL) PROVANCE,
DECODE(T.DIST_TYPE, 2, T.DIST_NAME, NULL) CITY,
DECODE(T.DIST_TYPE, 3, T.DIST_NAME, NULL) DISTRICT,
T.DIST_TYPE
FROM T
START WITH DIST_ID = 2
CONNECT BY T.PARENT_ID = PRIOR T.DIST_ID;
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
)
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;