接上一章SQLServer触发器递归更新
上一章讲了触发更新节点的PATHS,这一章讲这个PATHS的应用
1、知道某个节点,查询其所有子节点(非递归)
SELECT DISTINCT T2.TEAM_ID FROM
(
SELECT T1.* FROM
(
SELECT T.* FROM CFG_TEAM T
WHERE T.TEAM_ID = 1 --查询第1重条件满足的节点记录
) A
LEFT JOIN CFG_TEAM T1 ON A.TEAM_ID = T1.TEAM_ID OR A.TEAM_ID = T1.PARENT_ID
--关联到父节点
) B
LEFT JOIN CFG_TEAM T2 ON B.TEAM_ID = T2.TEAM_ID OR B.TEAM_ID = T2.PARENT_ID OR T2.PATHS LIKE B.PATHS +'.%'
--利用PATH快速搜索所有满足条件的子节点
2、知道某个子节点,查询某祖先节点(向上递归)
;WITH CTE AS (
SELECT T.TEAM_ID, T.PARENT_ID FROM CFG_TEAM T
WHERE T.TEAM_ID = 6--查询节点6的所有祖先ID
), RET (TEAM_ID, PARENT_ID) AS (
SELECT * FROM CTE
UNION ALL
SELECT T.TEAM_ID, T.PARENT_ID FROM CFG_TEAM T, RET B
WHERE B.PARENT_ID = T.TEAM_ID
)
SELECT TEAM_ID FROM RET