SQL with etc获取父节点或子节点总结

View Code

 1 获取节点的所有父节点
 2 ;with 
 3 #tmp   as( 
 4 select   *   from   tb
 5 where  id   =  'DMA20120327036'
 6 union   all 
 7 select   a.*   from   tb a,   #tmp   b 
 8 where   a.id   =   b.pid
 9 ) 
10 select   *   from   #tmp
11 
12 获取节点的所有子节点
13 ;with 
14 #tmp   as( 
15 select   *   from   tb
16 where  id   =  'DMA20120327036'
17 union   all 
18 select   a.*   from   tb a,   #tmp   b 
19 where   a.pid   =   b.id
20 ) 
21 select   *   from   #tmp
22 
23 搞好好久总结出来的……

 

转载于:https://www.cnblogs.com/comcyd/archive/2012/04/19/2456425.html