递归CTE查找所有项目的所有祖先 [英] Recursive CTE to find all ancestors OF ALL ITEMS

问题描述

我有一个简单的层次结构，需要能够生成一个与表中所有祖先相匹配的表的每个表。 （强调一下，这不是一个重复的问题！）

I have a simple hierarchy and need to be able to generate a single table that matches EACH item in the table with ALL of its ancestors. (Caps to emphasize that this is not a duplicate question!)

所以这是一张表：

Select Item='A', Parent=null into Items union
Select Item='B', Parent='A'  union
Select Item='C', Parent='A'  union
Select Item='D', Parent='B'  union
Select Item='E', Parent='B'  union
Select Item='F', Parent='C'  union
Select Item='G', Parent='C'  union
Select Item='H', Parent='D'  
Go

...表示此层次结构：

... which represents this hierarchy:

       A
     /   \
    B     C
   / \   / \
   D E   F G
  /
  H

所以B有一个祖先（A），而H有3个祖先（D，B，A）。这是所需的输出：

So B has one ancestor (A), and H has 3 ancestors (D,B,A). This is the desired output:

 Item | Ancestor
 B    | A
 C    | A
 D    | A
 D    | B
 E    | A
 E    | B
 F    | A
 F    | C
 G    | A
 G    | C
 H    | A
 H    | B
 H    | D

使用递归CTE，我可以找到任何一项的所有后代...

Using a recursive CTE, I'm able to find all descendants for any ONE item...

Create Function ItemDescendants(@Item char) Returns @result Table(Item char) As Begin
    ; With AllDescendants as (
        Select
            Item,
            Parent
        From Items i
        Where Item=@Item
        UNION ALL
        Select
            i.Item,
            i.Parent
        from Items i
        Join AllDescendants a on i.Parent=a.Item
    )
    Insert into @result (Item)
    Select Item from AllDescendants
    Where Item<>@Item;
    Return;
End
Go

...然后获取完整的扩展列表，我不得不求助于游标（yuk！）：

... but then to get the full expanded list, I have to resort to a cursor (yuk!):

Select Item, Parent into #t From Items

Declare @Item char
Declare c Cursor for (Select Item from Items)
Open c
Fetch c into @Item
While (@@Fetch_Status=0) Begin
    Insert into #t (Item, Ancestor) Select Item, @Item from dbo.ItemDescendants(@Item) 
    Fetch c into @Item
End
Close c
Deallocate c

Select Distinct
    Item,
    Ancestor
From #t
Where Parent is not null
Order by Item,Parent

Drop Table #t

这行得通，但是如果我能更快乐我可以用一个优雅的查询做到这一点。似乎应该可行-有任何想法吗？

This works, but I would be so much happier if I could do it with a single elegant query. Seems like it should be possible - any ideas?

推荐答案

假设我理解正确，它应该像从中向后递归一样简单叶节点（这很容易，因为表Items仅存储叶节点）：

Assuming I understand you right, it should be as simple as recursing backwards from the leaf nodes (which is easy since the table Items is storing only the leaf nodes):

;with AncestryTree as (
  select Item, Parent
  from Items
  where Parent is not null
  union all
  select Items.Item, t.Parent  
  from AncestryTree t 
  join Items on t.Item = Items.Parent
 )
select * from AncestryTree
order by Item, Parent

SQL Fiddle演示

这篇关于递归CTE查找所有项目的所有祖先的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！