Sql Server父子关系迭代查询SQL

 

 

类似ORACLE中的start with connect by prior语法。 

  www.2cto.com  

说明：T_SYS_ORG机构表，字段:ROW_ID/PAR_ROW_ID，PAR_ROW_ID表示父机构的ROW_ID 

 

--自上往下 

Sql代码  

WITH NODES     

 AS (   

 SELECT * FROM DBO.T_SYS_ORG par WHERE par.ROW_ID='28'  

 UNION ALL     

 SELECT child.* FROM T_SYS_ORG AS child INNER JOIN   

  NODES  AS RC ON child.PAR_ORG_ID = RC.ROW_ID)    

  SELECT * FROM T_SYS_ORG WHERE row_id IN (SELECT row_id  FROM NODES N )  ;  

 

--自下往上   www.2cto.com  

Sql代码  

   

 WITH NODES     

 AS (   

 SELECT * FROM DBO.T_SYS_ORG child WHERE child.ROW_ID='32'  

 UNION ALL     

 SELECT par.* FROM T_SYS_ORG AS par INNER JOIN   

  NODES  AS RC ON par.row_id = RC.PAR_ORG_ID)    

  SELECT * FROM T_SYS_ORG WHERE row_id IN (SELECT row_id  FROM NODES N )  ;