sql server迭代查询

三月阳光

类似ORACLE中的start with connect by prior语法。  
说明：T_SYS_ORG机构表，字段:ROW_ID/PAR_ROW_ID，PAR_ROW_ID表示父机构的ROW_ID
 
--自上往下
Sql代码
WITH parenttable    
 
 AS (  
 
 SELECT * FROM pcmc_dept par WHERE par.deptid='19' 
 
 UNION ALL    
 
 SELECT child.* FROM pcmc_dept AS child INNER JOIN  
 
  parenttable  AS RC ON child.pdeptid = RC.deptid)   
 
  SELECT * FROM parenttable WHERE deptid IN (SELECT deptid  FROM parenttable )  ;
 
--自下往上 
Sql代码
 
WITH NODES    
 
 AS (  
 
 SELECT * FROM pcmc_dept child WHERE deptid='34' 
 
 UNION ALL    
 
 SELECT par.* FROM pcmc_dept AS par INNER JOIN  
 
  NODES  AS RC ON par.deptid = RC.pdeptid )   
 
  SELECT * FROM NODES WHERE deptid IN (SELECT deptid  FROM pcmc_dept N where pdeptid='1')  ;