使用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