使用SQL Server CTE平层化系列 [英] Flattening Hierarchy series using SQL Server CTE
本文介绍了使用SQL Server CTE平层化系列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
寻找SQL Server CTE示例来创建层次结构,以便可以输出所有系列,如展平每个层次结构。例如在家庭树中如果根从grand开始父
我需要每个家庭memeber的层次结构列表的成员详细信息的列表+父的行详细信息
like
Looking for SQL Server CTE example to create hierarchy in such a way that I can output all the series like flattening the each hierarchy. e.g in family tree if root start from grand Parent I need list of each family memeber's hierarchy list with member details + parent's row details like
child1 row
child1 +Parent row
child1 + Grand parent Row
child2 Row
child2 + parent row
child2 + grand parent's row
and so on
CREATE TABLE Family(id int NULL,
Name varchar(20) null, Parent_id int NULL, level int NULL )
INSERT INTO Family VALUES
(1, 'Grand Parent',NULL, 1),
(2, 'Parent' , 1, 2),
(3, 'Child1' , 2, 3),
(4, 'Child2' , 2, 3)
select * from Family;
id Name Parent_id level
1 Grand Parent NULL 1
2 Parent 1 2
3 Child1 2 3
4 Child2 2 3
这是我可以做的。父行的详细信息在第5列和第6列。
This is what I can do at this point. Details of the parent row are in column 5 and 6.
with cte as
(
select ID,Parent_id,level,Name,id as parent_id,level, 'a' as type
from family
--where ID=3
union all
select f.ID,f.Parent_id,f.level,f.Name,c.id as parent_id,c.level, 'r' as type
from family f
inner join cte c
on f.parent_id=c.id
)
select * from cte order by id
结果应该是什么。 (注意第5和6列)
This is what the results should be. (note column 5 and 6)
Child_ID Parent_id Child_level Name parent_id level
1 NULL 1 Grand Parent 1 1
2 1 2 Parent 2 2
2 1 2 Parent 1 1
3 2 3 Child1 1 1
3 2 3 Child1 2 2
3 2 3 Child1 3 3
4 2 3 Child2 4 3
4 2 3 Child2 2 2
4 2 3 Child2 1 1
提前感谢。
推荐答案
with cte as (
select ID, Name, level, Parent_id
from family
union all
select cte.ID, cte.Name, cte.level, family.Parent_id
from cte
inner join family on cte.Parent_id = family.ID
)
select cte.ID as Child_ID, f2.Parent_ID, cte.level as Child_level, cte.Name, isnull(cte.Parent_id, cte.ID) as parent_ID, isnull(f.level, cte.level) as level
from cte
left outer join family f on cte.Parent_id = f.ID
inner join family f2 on cte.id = f2.ID
order by 1, 5
这篇关于使用SQL Server CTE平层化系列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文