小编典典

包含级别/距离列的闭包表INSERT语句

sql

我指的是Bill Karwin的演示文稿,以实现一个封闭表,这将有助于我管理层次结构。不幸的是,该演示文稿并未显示我如何插入/更新Level幻灯片67中提到的列;这将是非常有用的。我一直在想一想,但我无法提出我可以测试的具体内容。这是到目前为止我得到的:

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

我不确定该怎么做。有任何想法吗?


阅读 337

收藏
2021-03-08

共1个答案

小编典典

您知道对于Parent = self,您的Level = 0,并且当您从祖先复制路径时,您只是将Level增加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
2021-03-08