SQL 递归查询(根据指定的节点向上获取所有父节点,向下获取所有子节点)

目录 头条资讯

——————–01.向上查找所有父节点—————–
WITH TEMP AS
(
SELECT * FROM CO_Department WHERE ID=11    –表的ID
UNION ALL
SELECT T0.* FROM TEMP,CO_Department T0 WHERE TEMP.ParentID=T0.ID    –父级ID==子级ID
)
SELECT * FROM TEMP;

如图:根据”测试组”查找所有父节点

执行结果:

 

——————–02.向下查找所有子节点—————————-
WITH TEMP AS
(
SELECT * FROM CO_Department WHERE ID=11   –表的ID
UNION ALL
SELECT T0.* FROM TEMP,CO_Department T0 WHERE TEMP.ID=T0.ParentID    –子级ID==父级ID
)
SELECT * FROM TEMP;

如图:根据”开发组”查找所有子节点

执行结果: