Retreive所有的儿童和他们的孩子,递归SQL [英] Retreive all Children and their Children, recursive SQL
问题描述
考虑以下的行中一个数据库:
Consider the following rows in a database:
Id | Parent
__________________
1 null
2 1
3 2
4 3
5 null
6 5
每个编号
具有空
父
是所有者/超级家长。
Each Id
that has a null
Parent
is the "Owner"/"Super Parent".
什么是最好的办法,表现智慧,收集父母和他们的孩子?我应该使用 LINQ 或存储过程
What would be the best approach, performance wise, to collect the parents and their children ? Should I use LINQ or Stored Procedures ?
我想最终的结果是的IEnumerable< IEnumerable的< INT>>
推荐答案
在SQL中,你可以使用查询的CTE。例如,检索与其父节点的列表,并在其树的最高父
In SQL, you could query using a CTE. For example, to retrieve a list of nodes with their parent and the highest parent in their tree:
declare @t table (id int, parent int)
insert @t (id, parent) values (1, null), (2,1), (3,2), (4,3), (5,null), (6,5)
; with cte as (
select id, parent, id as head
from @t
where parent is null
union all
select child.id, child.parent, parent.head
from @t child
join cte parent
on parent.id = child.parent
)
select *
from cte
这给了:
id parent head
1 NULL 1
2 1 1
3 2 1
4 3 1
5 NULL 5
6 5 5
请注意,我改变你的例子数据,所以第2行,不再是自己一个孩子,但第1行的孩子。
Note that I changed your example data so row 2 is no longer a child of itself, but a child of row 1.
这篇关于Retreive所有的儿童和他们的孩子,递归SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!