具有排序顺序的sql父子树 [英] sql parent child tree with sort order
问题描述
假设这张表
ParentId ChildId SortOrder
---------------------------------
0 1 0
1 2 1
2 3 1
2 4 2
2 6 3
1 7 2
1 9 3
9 10 1
9 12 2
我将如何编写一个简单的 CTE(或其他类型的查询)来返回它们在任何级别的父/子关系,但保持父项下所有子项的排序顺序.
how would i write a simple CTE (or another type of query) that would return them parent/child relationships at any level but keeping the sort-order for all child-items below the parent.
基本上,这将是一个简单的树视图,其中包含按特定排序顺序排序的子项.
basically, this would be a simple tree-view with sub-items ordered by a specific sort-order.
sql server 2008 是数据库
sql server 2008 is the db
更新:可以有无限的父/子级别 - 我尝试了其他堆栈问题/答案中的一些示例,但没有一个包含子级别的排序顺序.
update: there can be infinite parent/child levels - i tried some examples from other stack question/answers but none included a sortorder at the child level.
示例结果应该是:
parent (sort 0)
child (sort 1)
child (sort 2)
child (sort 3)
child-child (sort 1)
child (sort 4)
child-child (sort 1)
child-child (sort 2)
等
希望这是有道理的——也许我看错了,这种结果最好在中间层构建?
hope this makes sense - maybe i'm looking at this wrong and this sort of result is better to be constructed within the mid-tier?
无论如何,非常感谢任何反馈
anyway, any feedback is greatly appreciated
推荐答案
具有特殊排序覆盖的递归 CTE.注意 2 的孩子中的排序覆盖(我稍微修改了源表以测试这种能力)
Recursive CTE with special sort override. Note the sort override in the children of 2 (I modified the source table slightly to test this ability)
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
)
select
tree=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))+')'
end
from cte
where depth>0
order by agg
option (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)
这篇关于具有排序顺序的sql父子树的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!