SQL根据单个公司ID查询父节点、子节点及兄弟节点方法

基础表信息 #

存在名为Company的公司层级表，存储公司ID、所属父公司ID、公司名称三个字段，测试数据如下：

CompanyId       MotherCompanyId   CompanyName     
----------      ------------     ------------   
  1             NULL             HpTopMother             
  2             1                HpTopDaughter1           
  3             1                HpTopDaughter2           
  4             NULL             HpTopDaughter3  
  5             2                HpTopDaughter4           
  6             1                HpTopDaughter5

需求规则 #

传入任意CompanyId，需要返回三类关联公司的ID：

• 当前公司的父公司（父公司存在时才返回）
• 当前公司下所有层级的子公司
• 当前公司的所有同级兄弟公司

预期效果：

• 传入CompanyId=1时，返回ID：2、3、6
• 传入CompanyId=2时，返回ID：1、3、5、6

原有代码问题 #

原有SQL存在三个明显错误，导致结果不符合预期：

• 代码中未定义@corId变量，子公司查询的条件完全无效
• 逻辑中没有编写查询父公司的部分，无法返回父公司ID
• 递归CTE写法错误，没有锚点成员和递归成员的关联逻辑，即使变量定义正确也无法查询多层子公司，同时兄弟公司查询也没有排除当前公司自身

正确可运行SQL #

DECLARE @cmpId BIGINT
SET @cmpId = 2; -- 此处替换为实际传入的公司ID

-- 提前查询当前公司的父公司ID
DECLARE @parentId BIGINT
SELECT @parentId = MotherCompanyId FROM Company WHERE CompanyId = @cmpId

;WITH CTE_CHILD AS (
    -- 锚点：查询当前公司的直接子公司
    SELECT CompanyId FROM Company WHERE MotherCompanyId = @cmpId
    UNION ALL
    -- 递归：遍历所有多层级子公司
    SELECT c.CompanyId
    FROM Company c
    INNER JOIN CTE_CHILD cc ON c.MotherCompanyId = cc.CompanyId
)
-- 合并三类查询结果
SELECT CompanyId
FROM (
    -- 查询父公司
    SELECT CompanyId FROM Company WHERE CompanyId = @parentId
    UNION
    -- 查询同级兄弟公司，排除自身
    SELECT CompanyId FROM Company WHERE MotherCompanyId = @parentId AND CompanyId <> @cmpId
    UNION
    -- 查询所有子公司
    SELECT CompanyId FROM CTE_CHILD
) res
WHERE CompanyId IS NOT NULL

代码验证结果：

• 传入@cmpId=1时，@parentId为NULL，父公司、兄弟公司查询无结果，仅返回所有子公司2、3、6，符合预期
• 传入@cmpId=2时，返回父公司1、兄弟公司3和6、子公司5，结果为1、3、5、6，符合预期

内容的提问来源于stack exchange，提问作者refresh