SQL根据指定节点ID获取所有父级节点和子级节点

--根据指定节点ID获取所有子节点--
WITH TEMP AS
(
SELECT * FROM table_name WHERE Id='4' --表的主键ID
UNION ALL
SELECT T0.* FROM TEMP,table_name T0 WHERE TEMP.Id=T0.ParentId --子级ID==父级ID
)
SELECT * FROM TEMP;


--根据指定节点ID获取所有父节点--
WITH TEMP AS
(
SELECT * FROM table_name WHERE Id='32' --表的主键ID
UNION ALL
SELECT T0.* FROM TEMP,table_name T0 WHERE TEMP.ParentId=T0.Id --父级ID==子级ID
)
SELECT * FROM TEMP;


--多个WITH AS组合使用场景

WITH TEMP AS
(
SELECT
*
FROM cb_DbSpecialTarget WHERE DbSpecialTargetName LIKE '%砌块含量指标%' AND DbSpecialTargetType=1
UNION ALL
SELECT
T0.*
FROM TEMP,cb_DbSpecialTarget T0 WHERE TEMP.ParentGUID=T0.DbSpecialTargetGUID
)
,
TEMP2 AS
(
SELECT *
FROM cb_DbSpecialTarget WHERE DbSpecialTargetName LIKE '%砌块含量指标%' AND DbSpecialTargetType=1
UNION ALL
SELECT
T0.*
FROM TEMP2,cb_DbSpecialTarget T0 WHERE TEMP2.DbSpecialTargetGUID=T0.ParentGUID
)

SELECT * FROM TEMP
UNION
SELECT * FROM TEMP2 ORDER BY HierarchySort