Sql Server父子关系迭代查询SQL
类似ORACLE中的start with connect by prior语法。说明：T_SYS_ORG机构表，字段:ROW_ID/PAR_ROW_ID，PAR_ROW_ID表示父机构的ROW_ID
--自上往下
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
NODESAS RC ON child.PAR_ORG_ID = RC.ROW_ID)
SELECT * FROM T_SYS_ORG WHERE row_id IN (SELECT row_idFROM NODES N );
--自下往上
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
NODESAS RC ON par.row_id = RC.PAR_ORG_ID)
SELECT * FROM T_SYS_ORG WHERE row_id IN (SELECT row_idFROM NODES N );
页:
[1]