包含级别/距离列的闭包表INSERT语句 [英] Closure Table INSERT statement including the level/distance column
问题描述
我指的是 Bill Karwin的演示文稿,以实现封闭表,这将有助于我管理层次结构.不幸的是,该演示文稿并未显示我如何插入/更新幻灯片67中提到的Level
列;这将是非常有用的.我一直在想一想,但我无法提出我可以测试的具体内容.这是到目前为止我得到的:
I'm referring Bill Karwin's presentation in order to implement a closure table which will help me manage hierarchies. Unfortunately, the presentation does not show how I could insert/update the Level
column mentioned on slide 67; this would have been very useful. I've been giving it a thought but I couldn't come up with something concrete that I could test. Here's what I got so far:
create procedure USP_OrganizationUnitHierarchy_AddChild
@ParentId UNIQUEIDENTIFIER,
@NewChildId UNIQUEIDENTIFIER
AS
BEGIN
INSERT INTO [OrganizationUnitHierarchy]
(
[AncestorId],
[DescendantId],
[Level]
)
SELECT [AncestorId], @NewChildId, (here I need to get the count of ancestors that lead to the currently being selected ancestor through-out the tree)
FROM [OrganizationUnitHierarchy]
WHERE [DescendantId] = @ParentId
UNION ALL SELECT @NewChildId, @NewChildId
END
go
我不确定该怎么做.有什么想法吗?
I am not sure how I could do that. Any ideas?
推荐答案
您知道,对于Parent = self,您具有Level = 0,并且当您从祖先复制路径时,您只是将Level 1增加了.
You know that for Parent = self you have Level = 0 and when you copying paths from ancestor, you're just increasing Level by 1:
create procedure USP_OrganizationUnitHierarchy_AddChild
@ParentId UNIQUEIDENTIFIER,
@NewChildId UNIQUEIDENTIFIER
AS
BEGIN
INSERT INTO [OrganizationUnitHierarchy]
(
[AncestorId],
[DescendantId],
[Level]
)
SELECT [AncestorId], @NewChildId, [Level] + 1
FROM [OrganizationUnitHierarchy]
WHERE [DescendantId] = @ParentId
UNION ALL
SELECT @NewChildId, @NewChildId, 0
END
这篇关于包含级别/距离列的闭包表INSERT语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!