在子父结构员工表中查找所有叶子节点的根节点首个子节点

我有如下员工表：

emp_no  unq_key          emp_id          mgr_id
7518870 2244087948  2244087965  2244087948
7518870 2244087948  2333920909  2244087948
7518870 2244087948  2244087950  2244087948
7518870 2244087948  2244087948  2340921616
7518870 2244087948  2244087963  2244087948
7518870 2244087948  2244087962  2244087961
7518870 2244087948  2244088015  2244087951
7518870 2244087948  2358036768  2244087955
7518870 2244087948  2244087952  2244087951
7518870 2244087948  2244087953  2244087952
7518870 2244087948  2244460352  2244087965
7518870 2244087948  2244087957  2244087948
7518870 2244087948  2277248665  2277248664
7518870 2244087948  2277248667  2244087965
7518870 2244087948  2340921670  2244087955
7518870 2244087948  2244087959  2244087948
7518870 2244087948  2244087960  2244087948
7518870 2244087948  2352651692  2244087951
7518870 2244087948  2314063120  2244087955
7518870 2244087948  2244088014  2244087952
7518870 2244087948  2244087954  2244087951
7518870 2244087948  2244087956  2244087955
7518870 2244087948  2244087955  2244087951
7518870 2244087948  2277248664  2244087951
7518870 2244087948  2280898236  2277248645
7518870 2244087948  2277248645  2244087951
7518870 2244087948  2358036769  2244087948
7518870 2244087948  2244087958  2244087948
7518870 2244087948  2244088016  2244088015
7518870 2244087948  2244087951  2244087948

根节点是unq_key=emp_id的行，我需要为所有叶子节点添加一列，显示其对应的根节点的首个子节点。比如叶子节点2244088016对应的根节点首个子节点应为2244087951。

我尝试了以下代码，但无法正确获取首个子节点：

;WITH   tree_view AS (
    SELECT 
         emp_no
         ,unq_key
         ,emp_id
         ,mgr_id
         ,0 AS order_sequence
         ,0 AS generation_number
          
    FROM 
        #temp_employees
    WHERE 
        unq_key=emp_id 
    UNION ALL 
        SELECT 
            parent.emp_no,
             parent.unq_key,
             parent.emp_id,
             parent.mgr_id 
             ,order_sequence +1 AS order_sequence
             ,generation_number + 1 AS generation_number
        FROM 
            #temp_employees parent
            JOIN tree_view tv
          ON parent.mgr_id = tv.emp_id
    ) 
    SELECT
       RIGHT('------------',generation_number*3) + ' Emp :'+ cast( emp_id as varchar(100))
      ,*
    FROM 
        tree_view   
    ORDER BY order_sequence; 

仅需为查询结果添加根节点首个子节点的列，恳请帮助，谢谢！

要实现需求，需要在CTE中额外记录根节点的首个子节点信息，修改后的SQL代码如下：

;WITH tree_view AS (
    -- 初始根节点查询，同时获取根节点的首个子节点
    SELECT 
        emp_no,
        unq_key,
        emp_id,
        mgr_id,
        0 AS order_sequence,
        0 AS generation_number,
        -- 获取根节点的首个子节点（按emp_id排序取第一个，可按需调整排序规则）
        (SELECT TOP 1 emp_id 
         FROM #temp_employees 
         WHERE mgr_id = te.emp_id 
         ORDER BY emp_id) AS root_first_child
    FROM #temp_employees te
    WHERE unq_key = emp_id

    UNION ALL

    -- 递归查询，传递根节点的首个子节点值
    SELECT 
        parent.emp_no,
        parent.unq_key,
        parent.emp_id,
        parent.mgr_id,
        tv.order_sequence + 1 AS order_sequence,
        tv.generation_number + 1 AS generation_number,
        tv.root_first_child -- 直接继承父节点的根首个子节点值
    FROM #temp_employees parent
    JOIN tree_view tv ON parent.mgr_id = tv.emp_id
),
-- 筛选叶子节点：没有下属的节点
leaf_nodes AS (
    SELECT tv.*
    FROM tree_view tv
    WHERE NOT EXISTS (
        SELECT 1 
        FROM #temp_employees te 
        WHERE te.mgr_id = tv.emp_id
    )
)
-- 查询叶子节点及对应的根节点首个子节点
SELECT 
    RIGHT('------------', generation_number*3) + ' Emp :' + CAST(emp_id AS VARCHAR(100)) AS emp_hierarchy,
    emp_no,
    unq_key,
    emp_id,
    mgr_id,
    root_first_child AS 根节点首个子节点
FROM leaf_nodes
ORDER BY order_sequence;

关键说明： #

1. 初始CTE中通过子查询获取根节点的首个直接子节点，这里按emp_id排序取第一个，你可以根据实际业务需求调整排序字段。
2. 递归过程中，所有子节点都会继承根节点的首个子节点值，确保每个节点都能关联到对应的目标值。
3. 通过leaf_nodes CTE筛选出没有下属的叶子节点，最终只展示叶子节点的层级信息及新增的目标列。

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