如何用SQL查询关联公司链路直至找到'MAIN'主记录

你当前用于测试的基础查询语句如下：

SELECT
    parent_company_component_id
    ,company_component_id
    ,name
    ,valid_cpy_compnt_type_cs_name
FROM dbo.cs_company_component
WHERE company_component_id IN (10217,7726,3109)

需求实现思路 #

无需提前指定测试ID，通过递归CTE（通用表达式）遍历全量表的父子关联关系，从所有顶层MAIN类型的公司向下遍历所有关联子公司，最终返回每一条公司记录对应的顶层主公司关联信息。

实现代码 #

核心逻辑兼容所有支持递归CTE的数据库（SQL Server、MySQL 8.0+、PostgreSQL等），示例语法如下：

-- 注：SQL Server环境无需加RECURSIVE关键字，MySQL 8.0+/PostgreSQL需要在WITH后添加RECURSIVE
WITH company_hierarchy AS (
    -- 锚点成员：先提取所有顶层MAIN公司作为递归起点
    SELECT
        parent_company_component_id
        ,company_component_id
        ,name
        ,valid_cpy_compnt_type_cs_name
        -- 存储顶层MAIN公司的ID和名称，后续递归会一直携带该值
        ,company_component_id AS main_company_id
        ,name AS main_company_name
    FROM dbo.cs_company_component
    WHERE valid_cpy_compnt_type_cs_name = 'MAIN'
        -- 若顶层公司的判断规则是父ID为空，可补充条件：AND parent_company_component_id IS NULL

    UNION ALL

    -- 递归成员：遍历所有子公司，继承父节点携带的顶层MAIN信息
    SELECT
        c.parent_company_component_id
        ,c.company_component_id
        ,c.name
        ,c.valid_cpy_compnt_type_cs_name
        ,ch.main_company_id
        ,ch.main_company_name
    FROM dbo.cs_company_component c
    INNER JOIN company_hierarchy ch 
        ON c.parent_company_component_id = ch.company_component_id
)
-- 最终查询所有层级的公司和对应顶层MAIN的关联数据
SELECT * FROM company_hierarchy
-- 若需要限制递归深度避免循环关联，SQL Server环境可添加如下选项（例如最大递归100层）
-- OPTION (MAXRECURSION 100)

补充说明 #

• 若存在循环关联的异常数据，建议添加递归深度限制，避免查询报错
• 若需要自定义返回字段，可在最终查询中按需筛选company_hierarchy的字段即可

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