sql - Closure Table INSERT 语句包括级别/距离列

我指的是 Bill Karwin's presentation 以实现一个闭包表，这将帮助我管理层次结构。不幸的是，演示文稿没有展示我如何插入/更新幻灯片 67 中提到的 Level 列；这将非常有用。我一直在考虑，但我无法想出一些我可以测试的具体内容。这是我到目前为止所得到的:

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

我不知道我怎么能做到这一点。有任何想法吗？

最佳答案

你知道对于 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

关于sql - Closure Table INSERT 语句包括级别/距离列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18622698/