/*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='' 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=''
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=''
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
) a
WHERE a.id=b.id AND lv>1