用SQL描述树

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

 

 

转载于:https://www.cnblogs.com/siyunianhua/p/3449819.html