父/子层次结构树视图 [英] Parent/Child hierarchy tree view

问题描述

我有一个父母桌，像这样

I have a parents table looks like this

CHILD_ID | PARENT_ID | NAME
1        | Null      | Bill
2        | 1         | Jane
3        | 1         | Steve
4        | 2         | Ben
5        | 3         | Andrew

Id喜欢获得这样的结果集

Id like to get a result set like this

Bill
---Jane
------Ben
---Steve
------Andrew

我知道我需要进行排名查询才能对级别和自我加入进行排名，但是我在网上只能找到CTE递归

I know I need to do a rank query to rank the levels and a self join but all I can find on the net is CTE recursion

我以前曾在Oracle中完成过此任务，但在MS SQL中未完成

I have done this in Oracle before but not in MS SQL

推荐答案

有点hacky，可以改进，但希望它能显示出原理...

Bit hacky and can be improved but hopefully it shows the principle...

;with relation (childId, parentId, childName, [level], [orderSequence])  
as  
(  
select childId, parentId, childName, 0, cast(childId as varchar(20))  
from @parents  
where parentId is null  
union all  
select p.childId, p.parentId, r.[level]+1, cast(r.orderSequence + '_' + cast(p.childId as varchar) as varchar(20))  
from @parents p  
inner join relation r on p.parentId = r.childId  
)  

select right('----------', ([level]*3)) +childName  
from relation  
order by orderSequence

但是，如果您想避免递归，那么另一种方法是使用具有相关树结构信息的树表-请参见

If however you want to avoid recursion then an alternative approach is to implement a tree table with the relevant tree structure information - see http://www.sqlteam.com/article/more-trees-hierarchies-in-sql for a walk through

这篇关于父/子层次结构树视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！