查询SYS_ORG_TB树的层级

WITH N(SYS_ORG_ID,SYS_ORG_NAME,LEVEL) AS(
SELECT SYS_ORG_ID,SYS_ORG_NAME,1 AS LEVEL FROM SYS_ORG_TB WHERE SYS_ORG_UPID IS NULL
UNION ALL
SELECT T2.SYS_ORG_ID,T2.SYS_ORG_NAME, (T1.LEVEL + 1) AS LEVEL 
FROM N T1,SYS_ORG_TB T2
WHERE T1.SYS_ORG_ID=T2.SYS_ORG_UPID
)
SELECT MAX(LEVEL) FROM N 

 

posted @ 2018-05-23 10:11  _revolution  阅读(254)  评论(0编辑  收藏  举报