类似ORACLE中的start with connect by prior语法。
说明:T_SYS_ORG机构表,字段:ROW_ID/PAR_ROW_ID,PAR_ROW_ID表示父机构的ROW_ID
--[b]自上往下[/b]
--[b]自下往上[/b]
说明:T_SYS_ORG机构表,字段:ROW_ID/PAR_ROW_ID,PAR_ROW_ID表示父机构的ROW_ID
--[b]自上往下[/b]
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 ) ;
--[b]自下往上[/b]
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 ) ;