反转 cte 的排序顺序

Question

在Microsoft SQL Server中，以下内容有效，但会产生：

，

儿子，爸爸，爷爷，曾祖父

而我需要它说：

曾祖父，

祖父，爸爸，儿子

declare @Family Table(
ID Int Identity(100,1) Primary Key
,Person varchar(128)
,ParentID Int default 0
)
insert into @Family(Person,ParentID) values('Great Granddad',0)
insert into @Family(Person,ParentID) values('Granddad',100)
insert into @Family(Person,ParentID) values('Dad',101)
insert into @Family(Person,ParentID) values('Son',102)
DECLARE @ID Int = 103
;with cte1 as (
    select 
         @ID AS cteID
        ,ID
        ,ParentID
        ,Person as ctePerson
    from @Family
    where ID = @ID -- this is the starting point you want in your recursion
    UNION ALL
    select @ID, F.ID, F.ParentID, F.Person
    from @Family F
    join cte1 P on P.ParentID = F.ID -- this is the recursion
) 
-- cte2 can reverse the sort order based on something built in (OVER?)
-- ROW_NUMBER() OVER(ORDER BY ? DESC) AS Row
,cte3 AS(
    select ID as cte3ID,(
        SELECT ',' + ctePerson
        FROM cte1
        WHERE cteID = F.ID
        FOR XML PATH ('')
        ) as People
    from @Family F
    where ID=@ID
)
SELECT * FROM CTE3

Answer 1

我不会使用另一个 CTE 对递归 CTE 的结果进行排序，因为 CTE 的结果在语义上是表，因此不能保证顺序。相反，在从 CTE 中进行选择时进行排序，就像普通表一样。

我建议插入一个表示级别或关系和顺序的字段：

;with cte1 as (
    select 
         @ID AS cteID
        ,ID
        ,ParentID
        ,Person as ctePerson
        ,0 lvl -- starting level with 0
    from @Family
    where ID = @ID -- this is the starting point you want in your recursion
    UNION ALL
    select @ID, F.ID, F.ParentID, F.Person
    , lvl + 1 -- increase level by 1
    from @Family F
    join cte1 P on P.ParentID = F.ID -- this is the recursion
) 
,cte3 AS(
    select ID as cte3ID,STUFF(( -- stuff removes the first ','
        SELECT ',' + ctePerson
        FROM cte1
        WHERE cteID = F.ID
        ORDER by lvl DESC -- order by level DESC to start with latest ancestor
        FOR XML PATH ('')
        ), 1, 1, '') as People
    from @Family F
    where ID=@ID
)
SELECT * FROM CTE3