17,377
社区成员
发帖
与我相关
我的任务
分享
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
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
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
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>
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;
我也写个