--根据节点找出本节点及其所有子节点
WITH Child_Tbl AS(
SELECT CompanyID,CompanyName,ParentID,CompLevel FROM U_Companies WHERE CompanyID = '80046'
UNION ALL
SELECT T1.CompanyID,T1.CompanyName,T1.ParentID,T1.CompLevel
FROM U_Companies T1 INNER JOIN Child_Tbl T2 ON T1.ParentID = T2.CompanyID
)
SELECT * FROM Child_Tbl
--根据孩子找到所有父节点
WITH Parent_Tb AS(
SELECT CompanyID,CompanyName,ParentID,CompLevel FROM U_Companies WHERE CompanyID = '80941'
UNION ALL
SELECT T1.CompanyID,T1.CompanyName,T1.ParentID,T1.CompLevel FROM U_Companies T1 INNER JOIN Parent_Tb T2 ON T1.CompanyID=T2.ParentID
)
SELECT * FROM Parent_Tb ORDER BY CompLevel
