sql父子树的排序顺序

具有特殊排序覆盖的递归CTE。请注意2的子级中的排序覆盖（我对源表进行了少许修改以测试此功能）

declare @relations table(ParentID int, ChildID int, SortOrder int, treeID int);insert into @relations values(0,1,0,0), (1,2,1,0), (2,3,2,0), (2,4,1,0), (2,6,3,0), (1,7,2,0), (1,9,3,0), (9,10,1,0), (9,12,2,0) --tree 0, (0,1,0,1), (1,2,1,1), (2,3,2,1), (2,4,1,1), (2,6,3,1), (1,7,2,1), (1,9,3,1), (9,10,1,1), (9,12,2,1) --tree 1; with cte(ParentId,ChildId,SortOrder,depth,agg,treeID) as (    select null,ParentId,SortOrder,0    , right('0000000'+CAST(treeID as varchar(max)),7)        +right('0000000'+CAST(SortOrder as varchar(max)),7)    , treeID    from @relations where ParentId=0    union all    select cte.ChildId,r.ChildId,r.SortOrder,cte.depth+1    , cte.agg        +right('0000000'+CAST(r.treeID as varchar(max)),7)        +right('0000000'+CAST(r.SortOrder as varchar(max)),7)        +right('0000000'+CAST(r.ChildId as varchar(max)),7)    , r.treeID    from cte    inner join @relations r on r.ParentID=cte.ChildId    where cte.depth<32767    and r.treeID=cte.treeID)selecttree=case depth when 1 then cast(ParentID as varchar(30))+' (sort '+cast(SortOrder as varchar(30))+')'    else REPLICATE(CHAr(9),depth-1)        + cast(ChildId as varchar(30))+' (sort '+cast(SortOrder as varchar(30))+')'    endfrom ctewhere depth>0order by aggoption (maxrecursion 32767);

结果：

tree--------------------------------------------------0 (sort 0)    2 (sort 1)        4 (sort 1)        3 (sort 2)        6 (sort 3)    7 (sort 2)    9 (sort 3)        10 (sort 1)        12 (sort 2)0 (sort 0)    2 (sort 1)        4 (sort 1)        3 (sort 2)        6 (sort 3)    7 (sort 2)    9 (sort 3)        10 (sort 1)        12 (sort 2)