Dowemo
0 0 0 0

-- 查找 E006002 的所有父节点和级别[最近的为1级]WITH CTE_PW (fPID,fFPID,lev) AS
(
 SELECT fPID,fFPID,0AS lev FROM APP_POSITION WHERE fPID = 'E006002'UNIONALLSELECT E.fPID,E.fFPID,lev + 1FROM 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,0AS lev FROM APP_POSITION WHERE fPID = 'E006002'UNIONALLSELECT E.fPID,E.fFPID,lev + 1FROM 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



Copyright © 2011 Dowemo All rights reserved.    Creative Commons   AboutUs