如何从表中获取依赖关系树。 [英] How do I get the dependency tree from a table.

问题描述

您好

请帮我以下要求。



我有一张桌子，其中有两列



ChildColumn - > ParentColumn

---------------------------

100 - > 101

100 - > 102

101 - > 103

102 - > 104

102 - > 105

103 - > 106

107 - > 108



我想取ParentColumn值为100.此外，父列值的相互依赖性也是如此。意味着100的直接父值是101和102由于101和102取决于103,104和105最终103取决于106.因此我想要输出如下。



输出

------

101

102

103

104

105

$

基本上我想要一个依赖输出树。请帮帮我。



谢谢和问候，

Mathi

解决方案

< blockquote>这正是递归CTE 的用途：

   cte  as  
（
 选择 * 来自 mytable  where  child =  100  
 
  union  < span class =code-keyword> all  
 
  select  t。* 来自 mytable t 
 内部  join  cte  on  cte.parent = t.child 
）
 选择 parent 来自 cte 

Tomas Takac的解决方案1非常好。我想添加一些额外的链接：

使用common_table_expression（Transact-SQL ） [ ^ ]

使用公用表表达式的递归查询 [ ^ ]

公用表格式 [ ^ ]

CTE在SQL Server中 [ ^ ]

Hi
Please help me in the below requirement.

I have a table in which two columns will be there

ChildColumn -> ParentColumn
---------------------------
100 -> 101
100 -> 102
101 -> 103
102 -> 104
102 -> 105
103 -> 106
107 -> 108

I want to take the ParentColumn value of 100. Also the inter-dependencies of the parent column values too.Meaning for 100 the direct parent value is 101 and 102. Since 101 and 102 are depending on 103, 104 and 105 finally 103 depends on 106. Hence I wanted the output as below.

Output
------
101
102
103
104
105
106
Basically I wanted an output of dependency tree. Please help me in this.

Thanks & Regards,
Mathi

解决方案

This is exactly what recursive CTE is for:

with cte as
(
  select * from mytable where child = 100
  
  union all
  
  select t.* from mytable t
  inner join cte on cte.parent = t.child
)
select parent from cte

Solution 1 by Tomas Takac is very good. I'd like to add some extra links:
WITH common_table_expression (Transact-SQL)[^]
Recursive Queries Using Common Table Expressions[^]
Common Table Expressions[^]
CTE In SQL Server[^]

这篇关于如何从表中获取依赖关系树。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！