再求层次结构的PL/SQL 查询语句，新要求

Question

SQL> SELECT TEST_QUERY.*,LEVEL, CONNECT_BY_ISLEAF
  2  FROM TEST_QUERY
  3  where (CASE CONNECT_BY_ISLEAF WHEN 1 THEN IS_FOLDER ELSE 0 END) = 0
  4  START WITH TEST_QUERY.PID = '0'
  5  CONNECT BY PRIOR TEST_QUERY.ID = TEST_QUERY.PID
  6  /
 
ID         PID        NAME                                                                             IS_FOLDER      LEVEL CONNECT_BY_ISLEAF
---------- ---------- -------------------------------------------------------------------------------- --------- ---------- -----------------
0000       0          A                                                                                        1          1                 0
0003       0000       A03                                                                                      1          2                 0
0007       0003       A03-01                                                                                   0          3                 1
0008       0003       A03-02                                                                                   0          3                 1
0009       0003       A03-03                                                                                   0          3                 1
0004       0000       A04                                                                                      0          2                 1
0005       0000       A05                                                                                      0          2                 1
0006       0000       A06                                                                                      0          2                 0
0010       0006       A06-01                                                                                   0          3                 1
0011       0006       A06-02                                                                                   0          3                 1
0012       0006       A06-03                                                                                   1          3                 0
0013       0012       A06-03-01                                                                                0          4                 1
0014       0012       A06-03-02                                                                                0          4                 1
0015       0012       A06-03-03                                                                                0          4                 1
 
14 rows selected
 
SQL> 

Answer 1

感谢大神，我也写了一个语句实现了，不过没有大神的简介，供参考select test_query.*,LEVEL
from test_query 
where id not in
(
     SELECT ID 
     FROM   Test_Query 
     WHERE  is_folder=1 
           and id not in(select distinct pid from test_query)
)
START WITH PID='0'
connect by prior id=pid

Answer 2

这个不能满足假如叶子不为文件，树枝不查询出的效果吧。
例如 0016 0000 A07 1
0017 0016 A07-01 1
以上sql会把0016查询出来。我的思路是先查询出所有叶子节点为文件夹的id，反向查询父节点。SELECT distinct id,pid,name,is_folder
FROM test_query 
START WITH id in (
select id from(
SELECT id,is_folder,
CONNECT_BY_ISLEAF "ISLEAF"
FROM test_query 
START WITH pid = '0'
CONNECT BY PRIOR id = pid ) t
where t.isleaf = 1 and t.is_folder=0
)
CONNECT BY PRIOR pid = id
order by id

Answer 3

我用我自己的脚本 和 您的脚本都试过，然后都发现了问题，比如A目录下有B目录，B目录下又有C目录，C目录下又有D目录，但是A、B、C、D目录下都没有文件，用我们的脚本都会把A给查出来~~~~

Answer 4

这个真没有，已经按照兄台的思路改过来了，反向从树叶开始，查找所有树叶涉及的树枝，然后过滤掉不在这些范围内的树枝，即剪掉了没有树叶的树枝，然后再分层~~~搞定 ，嘎嘎
脚本如下：SELECT TEST_QUERY.*,LEVEL
FROM TEST_QUERY
WHERE ID NOT IN(
  SELECT ID 
  FROM TEST_QUERY
  WHERE IS_FOLDER=1
       AND ID NOT IN
        (
        SELECT DISTINCT PID 
        FROM TEST_QUERY
        WHERE 1=1
        START WITH IS_FOLDER=0
        CONNECT BY PRIOR PID=ID
        )
  )
START WITH PID='0'
CONNECT BY PRIOR ID=PID

Answer 5

都不错，z_shousi是根据符合条件的树叶反向查，happyxiaowuge00gou是剔除不符合条件之后查全部。可谓殊途同归

Answer 6

SELECT test_query.*
,LEVEL
FROM test_query
WHERE ID IN (
SELECT PID
FROM test_query
START WITH is_folder=0
CONNECT BY PRIOR ID=pid 
UNION 
SELECT ID
FROM  test_query
WHERE is_folder=0
)
START WITH pid='0'
CONNECT BY PRIOR ID= pid;
我也写个