/*40条(1层)
SELECT COUNT(*) FROM t01_mwfl WHERE AZFSDM=01 AND LEVEL=1 START WITH PID=0 CONNECT BY PRIOR ID=PID
*/
SELECT * FROM t01_mwfl
WHERE AZFSDM
=01 AND LEVEL=1 START
WITH PID
=0 CONNECT
BY PRIOR ID
=PID
SELECT LEVEL FROM t01_mwfl
WHERE AZFSDM
=01 AND LEVEL=1 START
WITH PID
=0 CONNECT
BY PRIOR ID
=PID
/* 210条(2层)
SELECT COUNT(*) FROM t01_mwfl WHERE LEVEL =2 AND AZFSDM=01 START WITH PID=0 CONNECT BY PRIOR ID=PID
*/
SELECT * FROM t01_mwfl
WHERE LEVEL =2 AND AZFSDM
=01 START
WITH PID
=0 CONNECT
BY PRIOR ID
=PID
SELECT LEVEL FROM t01_mwfl
WHERE LEVEL =2 AND AZFSDM
=01 START
WITH PID
=0 CONNECT
BY PRIOR ID
=PID
/*395条(3层)
SELECT COUNT(*) FROM t01_mwfl WHERE LEVEL=3 AND AZFSDM=01 START WITH PID=0 CONNECT BY PRIOR ID=PID
*/
SELECT * FROM t01_mwfl
WHERE LEVEL=3 AND AZFSDM
=01 START
WITH PID
=0 CONNECT
BY PRIOR ID
=PID
SELECT LEVEL FROM t01_mwfl
WHERE LEVEL=3 AND AZFSDM
=01 START
WITH PID
=0 CONNECT
BY PRIOR ID
=PID
select * from WQGM_2V.t01_mwfl
where azfsdm
='01' and pid
=0 and id
in
(
--通过字符串处理
select substr(substr(path,
2),
1,instr(substr(path,
2),
'/')
-1) pid
from (
--取大于某层的树路径
SELECT sys_connect_by_path(id,
'/')
as path
FROM WQGM_2V.t01_mwfl
WHERE LEVEL>2 AND AZFSDM
=01 START
WITH PID
=0 CONNECT
BY PRIOR ID
=PID
)
)
/*三层*/
/**/
SELECT ID,NAME,ROWNUM
FROM WQGM_2V.t01_mwfl
WHERE PID
=0
AND AZFSDM
='01'
AND ID
IN
(
SELECT ID
FROM WQGM_2V.t01_mwfl MINUS
(
SELECT ID
FROM WQGM_2V.t01_mwfl MINUS
SELECT PID
FROM WQGM_2V.t01_mwfl
)
)
ORDER BY ID
/**/
SELECT ID,NAME,ROWNUM,
LEVEL FROM WQGM.t01_mwfl
WHERE PID
=0
AND AZFSDM
='01'
AND ID
IN
(
SELECT ID
FROM WQGM.t01_mwfl MINUS
(
SELECT ID
FROM WQGM.t01_mwfl MINUS
SELECT PID
FROM WQGM.t01_mwfl
)
)
START WITH PID
=0 CONNECT
BY PRIOR ID
=PID
ORDER BY ID
/*四层*/
SELECT b.id,a.path,a.name FROM ( SELECT id FROM WQGM.t01_mwfl MINUS ( SELECT id FROM WQGM.t01_mwfl MINUS SELECT pid FROM WQGM.t01_mwfl )) b,(SELECT id,sys_connect_by_path(id,'/') as path,sys_connect_by_path(name,'/') as name,level as lv FROM WQGM.t01_mwfl WHERE azfsdm=01 START WITH pid=0 CONNECT BY PRIOR id=pid ) aWHERE a.id=b.id AND lv>1
转载于:https://www.cnblogs.com/siyunianhua/p/3449819.html
