oracle树形结构表查询语句

现有表：
id（不重复）   父级id         地区名字         级别
dist_id             parent_id      dist_name     dist_type
1                           0                        中国               0
2                           1                        北京               1
52                         2                        北京               2
500                      52                     朝阳区            3
501                      52                     东城区            3
3                          1                          安徽              1
36                        3                          安庆             2
399                     36                         大观区         3
……………………………………………………
就是这样的表，省市区都在一张表里面，现在想查询出这样的：
id             父级id          省            市            区              级别
2                     1                         北京                              1
52                   2                        北京                               2
500                52                       北京          朝阳区        3
501                52                       北京          东城区        3

请各位大侠帮忙sql语句如何写才能实现这个结果，50高分悬赏

6 个解决方案

#1

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

#2

引用 楼主 fanjinruo 的回复:

现有表：
id（不重复）   父级id         地区名字         级别
dist_id             parent_id      dist_name     dist_type
1                           0                        中国               0
2                           1                        北京               1
52                         2                        北京               2
500                      52                     朝阳区            3
501                      52                     东城区            3
3                          1                          安徽              1
36                        3                          安庆             2
399                     36                         大观区         3
……………………………………………………
就是这样的表，省市区都在一张表里面，现在想查询出这样的：
id             父级id          省            市            区              级别
2                     1                         北京                              1
52                   2                        北京                               2
500                52                       北京          朝阳区        3
501                52                       北京          东城区        3

请各位大侠帮忙sql语句如何写才能实现这个结果，50高分悬赏

第一个北京应该是在省里面。因为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;

#3

楼上zlloct的这种方式不太理想，因为数据库里面有全国的省市区县，这样查询会奔溃的，不知道zlloct是不是还有其他的意思？

#4

引用 3 楼 fanjinruo 的回复:

楼上zlloct的这种方式不太理想，因为数据库里面有全国的省市区县，这样查询会奔溃的，不知道zlloct是不是还有其他的意思？

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;

#5

引用 3 楼 fanjinruo 的回复:

楼上zlloct的这种方式不太理想，因为数据库里面有全国的省市区县，这样查询会奔溃的，不知道zlloct是不是还有其他的意思？

start with dist_id=2 这个条件就限定了只找北京的啊

#6

hidanger521的方式是对的，zlloct的方式有点问题，到区这条记录上就没有省市的值了