Server层次结构显示

Question

我想寻求有关SQL Server查询的帮助。

我有一个名为Students的表，列为"Id，Name，LeaderId“

Id  Name    LeaderId
1   Baldo       0
2   Cardo       1
9   Luningning  1
6   Pedro       1
3   Paolo       2
5   Reginaldo   4
4   Bernardo    5
7   Roberto     6
8   Narciso     6
10  Jopay       9
11  Policarpio  10
12  Sisa        10

例如，巴尔多没有领袖，因为他在等级结构的最顶端。卡多的领导，鲁宁宁，佩德罗是巴尔多。保罗的领袖是卡尔多。因为保罗的LeaderId是2。

请看下面我的截图样本。

​

​

我的问题是如何根据上面的图片显示等级结构的排名。

有困难的时候，尝试按leaderId分组，使用CTE加上自连接等等，但我无法获得预期的输出。

预期查询输出

Name Rank
Baldo 0
Cardo 1
Luningning 1
Pedro 1

Paolo 2
Roberto 2
Narcisco 2
Jopay 2

等等..。

提前谢谢你。

Answer 1

展示了用象形色色做这件事的另一种方法。我从SOS提供的答案中窃取递归CTE只是为了方便。我将稍作解释，为什么这是不必要的，以及您的应用程序如何能够直接维护层次结构的值。

with YourTable as (
    select * from (values 
        (1, 'Baldo',       0),
        (2, 'Cardo',       1),
        (9, 'Luningning',  1),
        (6, 'Pedro',       1),
        (3, 'Paolo',       2),
        (5, 'Reginaldo',   4),
        (4, 'Bernardo',    5),
        (7, 'Roberto',     6),
        (8, 'Narciso',     6),
        (10, 'Jopay',       9),
        (11, 'Policarpio',  10),
        (12, 'Sisa',        10)
    ) as x(Id, Name, LeaderId)
), tree AS (
    SELECT Id, Name, LeaderId, 
        h = cast(concat('/', Id, '/') as varchar(max)) 
    FROM   YourTable
    WHERE  LeaderId = 0

    UNION ALL

    SELECT c.Id, c.Name, c.LeaderId,
        h = concat(tree.h, c.Id, '/')
    FROM   YourTable c 
    INNER JOIN tree 
        ON c.LeaderId = tree.Id
), hid as (
    select Id, Name, LeaderId,
    h = cast(h as hierarchyid)
    from tree
)
SELECT *, 
    h.ToString(), 
    RankNum = h.GetLevel() - 1
FROM   hid
ORDER BY RankNum, h

通过对这个答案的阐述，我正在计算一个可以转换为层次结构的值。从这里，我将向您展示，您可以从层次结构I中获得所需的RankNum信息。

你为什么要这么做？在我的示例中，我必须在运行时计算层次结构的值。但是，在对数据进行更改时，应用程序也可以维护它！让我们看看三种类型的数据更改-插入、更新和删除。

首先，让我们摆好桌子。

/*drop table if exists dbo.Employees;*/
create table dbo.Employees (
    EmployeeId int identity
        constraint PK_Employees primary key clustered,
    Name varchar(100) not null,
    LeaderId int null,
    ReportingStructure hierarchyid null
);
create index IX_Employees__ReportingStructure
    on dbo.Employees (ReportingStructure);
go

这里唯一值得注意的是，层次结构数据类型是可索引的。稍后我们将在更新和删除员工时使用这一事实。接下来，让我们来处理如何创建新的员工记录：

create or alter procedure dbo.InsertEmployee (
    @Name varchar(100),
    @LeaderID int
)
as
begin
    set nocount on;
    declare @Id int, @ReportingStructure hierarchyid;
    declare @IdCatcher table (
        Id int
    );

    set @ReportingStructure = (
        select ReportingStructure
        from dbo.Employees
        where EmployeeId = @LeaderID
    );
    if (@ReportingStructure is null)
        set @ReportingStructure = '/';

    begin tran

        insert into dbo.Employees
            (Name, LeaderID)
        output inserted.EmployeeId
        into @IdCatcher
        values
            (@Name, @LeaderID);

        set @Id = (
            select Id
            from @IdCatcher
        );

        update dbo.Employees
        set ReportingStructure = concat(@ReportingStructure.ToString(), @Id, '/')
        where EmployeeId = @Id;

    commit
end
go

这是相当简单的。这些步骤是：

• 为传递的领袖找到ReportingStructure。
  • 如果没有发现，他们将启动自己的树，并使用他们的EmployeeID种子它

• 插入新记录并从中获取EmployeeID
• 用正确的ReportingStructure更新新记录。

接下来，我们将处理更新。注意，我们对这个练习感兴趣的唯一更新是对某人的经理的更新。也就是说，更改某人的名字并不会影响他们在组织结构图中的位置，所以我们可以在这里忽略它。

create or alter procedure dbo.UpdateEmployeeLeader (
    @EmployeeID int,
    @NewLeaderID int
)
as
begin
    set nocount on;
    declare @newManagerReportingStructure hierarchyid,
        @oldReportingStructure hierarchyid;

    set @newManagerReportingStructure = (
        select ReportingStructure
        from dbo.Employees
        where EmployeeId = @NewLeaderID
    );
    set @oldReportingStructure = (
        select ReportingStructure
        from dbo.Employees
        where EmployeeId = @EmployeeID
    );

    if (@oldReportingStructure is not null and @newManagerReportingStructure is not null)
    begin
        begin tran
            update dbo.Employees
            set LeaderId = @NewLeaderID
            where EmployeeId = @EmployeeID;

            with cte as (
                select ReportingStructure,
                    NewReportingStructure = 
                    ReportingStructure.GetReparentedValue(
                        @oldReportingStructure.GetAncestor(1),
                        @newManagerReportingStructure
                    )
                from dbo.Employees
                where ReportingStructure.IsDescendantOf(@oldReportingStructure) = 1
            )
            update cte
            set ReportingStructure = NewReportingStructure
        commit tran
    end
end
go

对这里发生的事情的简单解释是：

• 为新经理和正在移动的员工查找ReportingStructure
• 更新所请求的员工以及向其报告的所有员工(直接或间接)，以反映新的报告结构。

最后一点我觉得有必要解释一下。“所有向他们报告的员工(直接或间接)”是由ReportingStructure.IsDescendantOf(@oldReportingStructure) = 1代码完成的。注意，它还标识了员工本身(因为IsDescendantOf()认为值是他们自己的后代)。同样值得注意的是，对IsDescendantOf()的调用是有效的，因为我们在ReportingStructure列上创建了该索引。

“以反映新的报告结构”位是通过代码的GetReparentedValue()位来完成的。我喜欢把正在发生的事情想象成一个字符串，用一个新的主导部分取代了路径的某些主导部分。扩展树的比喻，就像你从一棵树上拿出一根树枝，然后把它嫁接到另一棵树上。

最后，让我们来处理删除。

create or alter procedure dbo.DeleteEmployee (
    @EmployeeID int
)
as
begin
    set nocount on;
    declare @ReportingStructure hierarchyid = (
        select ReportingStructure
        from dbo.Employees
        where EmployeeId = @EmployeeID
    );
    if not exists (
        select 1
        from dbo.Employees
        where ReportingStructure.IsDescendantOf(@ReportingStructure) = 1
            and EmployeeId <> @EmployeeID
    )
        delete dbo.Employees
        where EmployeeId = @EmployeeID;
    else
    begin
        raiserror(
            'Employee still has direct reports. Update their leader before deleting.',
            16,
            1
        );
    end
end
go

这很简单。我们只是在检查这个员工是否有任何报告(直接或其他，尽管前面的更新过程应该确保在删除之前我们没有任何“跳过”级别的数据)。

为了结束这一切，下面是对这些procs的调用，它们处理数据中的操作。我们雇几个员工吧！

exec dbo.InsertEmployee @name = 'Baldo', @LeaderID = 0;
exec dbo.InsertEmployee @name = 'Cardo', @LeaderID = 1;
exec dbo.InsertEmployee @name = 'Luningning', @LeaderID = 1;
exec dbo.InsertEmployee @name = 'Pedro', @LeaderID = 1;
exec dbo.InsertEmployee @name = 'Paolo', @LeaderID = 2;
exec dbo.InsertEmployee @name = 'Reginaldo', @LeaderID = 4;
exec dbo.InsertEmployee @name = 'Bernardo', @LeaderID = 5;
exec dbo.InsertEmployee @name = 'Roberto', @LeaderID = 6;
exec dbo.InsertEmployee @name = 'Narciso', @LeaderID = 6;
exec dbo.InsertEmployee @name = 'Jopay', @LeaderID = 9;
exec dbo.InsertEmployee @name = 'Policarpio', @LeaderID = 10;
exec dbo.InsertEmployee @name = 'Sisa', @LeaderID = 10;

坏消息..。雷吉纳尔多刚辞职。让我们把纳西索的经理升级为保罗

exec dbo.UpdateEmployeeLeader @EmployeeID = 9, @NewLeaderID = 5;
exec dbo.DeleteEmployee @EmployeeID = 6;

哦..。我们错过罗伯托了！让他现在也向保罗汇报吧。

exec dbo.UpdateEmployeeLeader @EmployeeID = 8, @NewLeaderID = 5;
exec dbo.DeleteEmployee @EmployeeID = 6;

关于这种方法，我要说的最后一件事是，我仍然喜欢让LeaderID成为表中的一列。为什么？我认为这是“谁向谁报告”的真相来源。而ReportingStructure列只是一条从叶子到树根的物化路径。如果ReportingStructure被破坏(即不能准确反映报告结构)发生了什么，我们可以从LeaderID/EmployeeID链接中重新计算它。