我有一个简单的层次结构,需要能够生成一个表,将表中的每个项目与其所有祖先相匹配。 (大写强调这不是一个重复的问题!)
所以这是一个表:
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
...代表这个层次结构:
A
/ \
B C
/ \ / \
D E F G
/
H
所以B有一个祖先(A),H有3个祖先(D,B,A)。这是所需的输出:
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,我能够找到任何一项的所有后代......
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
...但是为了获得完整的扩展列表,我必须求助于光标(哎呀!):
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
这是可行的,但如果我能用一个优雅的查询来完成它,我会更高兴。看起来应该是可能的 - 有什么想法吗?