如何用递归方式获取每行的关联父级与子级Client ID

可以实现该需求，通过递归CTE（公共表表达式）遍历客户层级关系，一次性为所有客户生成对应的关联ID拼接字符串，无需针对单个ID执行查询。

需求示例 #

解决方案SQL #

WITH recursive client_hierarchy AS (
    -- 基础层：定位所有根节点（无父级的客户），记录根ID和自身ID
    SELECT 
        client_id AS root_id,
        client_id
    FROM clients
    WHERE primary_client_id IS NULL
    UNION ALL
    -- 递归层：遍历子节点，继承父节点的根ID，建立完整层级关联
    SELECT 
        ch.root_id,
        c.client_id
    FROM clients c
    INNER JOIN client_hierarchy ch ON c.primary_client_id = ch.client_id
),
-- 按根节点分组，拼接该组下所有客户ID为有序字符串
root_id_groups AS (
    SELECT 
        root_id,
        GROUP_CONCAT(client_id ORDER BY client_id ASC) AS full_group_ids
    FROM client_hierarchy
    GROUP BY root_id
),
-- 为每个客户匹配其所属的根节点组
client_root_mapping AS (
    SELECT 
        c.client_id,
        c.primary_client_id,
        r.full_group_ids
    FROM clients c
    LEFT JOIN client_hierarchy ch ON c.client_id = ch.client_id
    LEFT JOIN root_id_groups r ON ch.root_id = r.root_id
)
-- 生成最终结果：根节点保留完整组，子节点移除自身ID
SELECT 
    client_id,
    primary_client_id,
    CASE
        -- 根节点直接返回完整分组字符串
        WHEN primary_client_id IS NULL THEN full_group_ids
        -- 子节点从完整分组中移除自身ID，处理不同位置的情况
        ELSE 
            CASE
                -- 分组仅包含自身（孤立节点）
                WHEN full_group_ids = CAST(client_id AS CHAR) THEN full_group_ids
                -- 自身在分组开头
                WHEN LEFT(full_group_ids, LENGTH(CAST(client_id AS CHAR))) = CAST(client_id AS CHAR)
                    THEN SUBSTRING(full_group_ids, LENGTH(CAST(client_id AS CHAR)) + 2)
                -- 自身在分组结尾
                WHEN RIGHT(full_group_ids, LENGTH(CAST(client_id AS CHAR))) = CAST(client_id AS CHAR)
                    THEN SUBSTRING(full_group_ids, 1, LENGTH(full_group_ids) - LENGTH(CAST(client_id AS CHAR)) - 1)
                -- 自身在分组中间
                ELSE REPLACE(full_group_ids, CONCAT(',', client_id, ','), ',')
            END
    END AS expected_result
FROM client_root_mapping
ORDER BY client_id;

逻辑说明 #

1. 递归层级遍历：通过client_hierarchy遍历所有客户，建立每个节点到其根节点的关联，收集根节点下的所有客户ID。
2. 分组拼接ID：root_id_groups按根节点分组，将同组的客户ID拼接成有序字符串。
3. 结果生成：关联原表和分组结果，对根节点直接返回完整分组；对子节点则从分组字符串中移除自身ID，得到符合预期的关联ID拼接结果。

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