再说ORACLE树查询

-- 查找 E006002 的所有父节点和级别[最近的为1级]
WITH CTE_PW (fPID,fFPID,lev) AS
(
    SELECT fPID,fFPID,0 AS lev FROM APP_POSITION WHERE fPID = 'E006002'
    UNION ALL
    SELECT E.fPID,E.fFPID,lev + 1 FROM CTE_PW T,APP_POSITION E
    WHERE T.fFPID = E.fPID
)
SELECT * FROM CTE_PW WHERE fPID <> 'E006002';
结果集合如下：
XH  FPID    FFPID   LEVEL
---------------------------
01  E006001 E003003 1
02  E003003 E003002 2
03  E003002 E003001 3
04  E003001 E000101 4
05  E000101 E000100 5
06  E000100 E000012 6
07  E000012 E000011 7
08  E000011 E000010 8
09  E000010 E000009 9
10  E000009 E000008 10
11  E000008 E000007 11
12  E000007 E000006 12
13  E000006 E000005 13
14  E000005 E000004 14
15  E000004 E000003 15
16  E000003 E000002 16
17  E000002 E000001 17
18  E000001 E000000 18
19  E000000 ROOT    19

-- 查找 E006002 的所有子节点和级别[最近的为1级]
WITH CTE_PW (fPID,fFPID,lev) AS
(
    SELECT fPID,fFPID,0 AS lev FROM APP_POSITION WHERE fPID = 'E006002'
    UNION ALL
    SELECT E.fPID,E.fFPID,lev + 1 FROM CTE_PW T,APP_POSITION E
    WHERE T.fPID = E.fFPID
)
SELECT * FROM CTE_PW WHERE fPID <> 'E006002';
结果集合如下：
XH  FPID        FFPID       LEVEL
----------------------------------
1   E006003     E006002     1
2   E006012     E006002     1
3   E100012329  E006002     1
4   E006004     E006003     2
5   E006013     E006012     2
6   E006014     E006012     2
7   E100012330  E100012329  2
8   E100012331  E100012330  3
9   E006060     E006014     3