如何反转CTE递归查询的结果顺序？

问题描述 #

现有一段从指定节点向上遍历所有父节点的SQL代码：

SELECT * INTO MyTable
FROM
(
    SELECT 1 Id, 1 ParentId, 'Parent A' Name
    UNION ALL
    SELECT 5,1,'Child A1'
    UNION ALL
    SELECT 47894,5,'Child A2'
    UNION ALL
    SELECT 2,2, 'Parent B'
    UNION ALL
    SELECT 3,2, 'Child B1'
)TAB

;With CTE as
(
  select * from MyTable where Id = 47894
  union all
  select a.* from MyTable a inner join cte b 
    on a.Id=b.ParentId and a.Id<>b.Id
)

select STRING_AGG(Name, ' >> ') from CTE

当输入Id=47894时，当前输出为：

Child A2 >> Child A1 >> Parent A

需要将结果反转，得到从根节点到目标节点的顺序：

Parent A >> Child A1 >> Child A2

解决方案 #

只需在递归CTE中添加层级深度字段，再通过STRING_AGG的排序参数控制拼接顺序即可：

修改后的完整代码：

SELECT * INTO MyTable
FROM
(
    SELECT 1 Id, 1 ParentId, 'Parent A' Name
    UNION ALL
    SELECT 5,1,'Child A1'
    UNION ALL
    SELECT 47894,5,'Child A2'
    UNION ALL
    SELECT 2,2, 'Parent B'
    UNION ALL
    SELECT 3,2, 'Child B1'
)TAB

;With CTE as
(
  -- 初始节点层级设为1
  select *, 1 as Depth from MyTable where Id = 47894
  union all
  -- 父节点层级=子节点层级+1，越上层的节点Depth值越大
  select a.*, b.Depth + 1 as Depth from MyTable a inner join cte b 
    on a.Id=b.ParentId and a.Id<>b.Id
)

-- 按Depth倒序拼接，根节点Depth最大，排在最前
select STRING_AGG(Name, ' >> ') WITHIN GROUP (ORDER BY Depth DESC) from CTE

核心逻辑 #

1. 给CTE添加Depth字段：目标节点的Depth为1，每向上遍历一层父节点，Depth加1
2. 在STRING_AGG中使用WITHIN GROUP (ORDER BY Depth DESC)，让层级最高的根节点先被拼接，最终得到从根到目标节点的顺序

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