生成HierarchyID

Question

我想插入像这样的hierarchyId

• / - CEO (Root)
  • /1/ - 采购经理
    • /1/1/ - 采购主管
  • /2/ - 销售经理
    • /2/1/ - 销售主管

这是我想要使用的层次结构，它是正确的吗？如果是，我该怎么做，任何人都可以给我一些代码片段。

Answer 1

我在搜索有关 hierarchyid 数据类型的信息时遇到了这个问题，并且认为对于我之后的其他人来说，看到将 hierarchyid 插入为的代码也会很有趣根据问题。

我并不是说这些是插入 hierarchyid 的唯一方法，但希望它能帮助那些像我一样以前没有使用此数据类型的经验的人。

使用此表，

create table OrgChart
(
    Position hierarchyid,
    Title nvarchar(50)
)

您可以使用 Parse 直接插入 hierarchyid 使用字符串路径：

insert into OrgChart(Position, Title) 
    values  (hierarchyid::Parse('/'), 'CEO'),
            (hierarchyid::Parse('/1/'), 'Purchase Manager'),
            (hierarchyid::Parse('/1/1/'), 'Purchase Executive'),
            (hierarchyid::Parse('/2/'), 'Sales Manager'),
            (hierarchyid::Parse('/2/1/'), 'Sales Executive')

并使用以下查询来检查表

select Position.ToString(), * from OrgChart

您还可以使用 hierarchyid 数据类型方法 GetRoot 和 GetDescendant 构建层次结构。我发现这种方法比较麻烦，但我认为如果您以编程方式管理层次结构，那么使用这些方法是必要的。

declare @root hierarchyid,
        @id hierarchyid

set @root = hierarchyid::GetRoot()

insert into OrgChart(Position, Title) values (@root, 'CEO')

set @id = @root.GetDescendant(null, null)
insert into OrgChart(Position, Title) values (@id, 'Purchase Manager')

set @id = @root.GetDescendant(@id, null)
insert into OrgChart(Position, Title) values (@id, 'Sales Manager')

select @id = Position.GetDescendant(null, null) from OrgChart where Title = 'Purchase Manager'    
insert into OrgChart(Position, Title) values (@id, 'Purchase Executive')

select @id = Position.GetDescendant(null, null) from OrgChart where Title = 'Sales Manager'    
insert into OrgChart(Position, Title) values (@id, 'Sales Executive')

一定要查看其他答案中提供的链接，但希望尝试此代码也会有所帮助。

Answer 2

假设您有一个带有自连接的表模式（如下所示），并且您的 CEO 的 ManagerID 为 NULL。

CREATE TABLE Employee
(
    EmployeeID int NOT NULL IDENTITY(1,1) PRIMARY KEY
    , JobTitle nvarchar(50) NOT NULL
    , FirstName nvarchar(50) NOT NULL
    , LastName nvarchar(50)
    , ManagerID int
) 

ALTER TABLE dbo.Employee ADD CONSTRAINT
    FK_Employee_Employee FOREIGN KEY
    (
    ManagerID
    ) REFERENCES dbo.Employee
    (
    EmployeeID
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

GO

INSERT INTO Employee(JobTitle, FirstName, LastName, ManagerID)
Values ('Executive', 'Supreme', 'Leader', NULL)

INSERT INTO Employee(JobTitle, FirstName, LastName, ManagerID)
Values ('Manger', 'Boss', 'Man', 1)

INSERT INTO Employee(JobTitle, FirstName, LastName, ManagerID)
Values ('Minion', 'Bob', 'Minion', 2)

INSERT INTO Employee(JobTitle, FirstName, LastName, ManagerID)
Values ('Minion', 'Joe', 'Minion', 2)

GO

您可以使用以下递归 CTE 自动生成一组初始的 hierarchyid 值：

;WITH EmployeeHierarchy (
    EmployeeHierarchyID
    , EmployeeID
    , JobTitle
    , LastName
    , FirstName
    , ManagerID
    )
AS (
    SELECT HIERARCHYID::GetRoot() AS EmployeeHierarchyID
        , EmployeeID
        , JobTitle
        , LastName
        , FirstName
        , ManagerID
    FROM Employee
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT HIERARCHYID::Parse(Manager.EmployeeHierarchyID.ToString() + (
                CONVERT(VARCHAR(20), ROW_NUMBER() OVER (
                        ORDER BY DirectReport.EmployeeID
                        ))
                ) + '/') AS EmployeeHierarchy
        , DirectReport.EmployeeID
        , DirectReport.JobTitle
        , DirectReport.LastName
        , DirectReport.FirstName
        , DirectReport.ManagerID
    FROM EmployeeHierarchy AS Manager
    INNER JOIN Employee AS DirectReport
        ON Manager.EmployeeID = DirectReport.ManagerID
    )
SELECT  EmployeeHierarchyID
    , EmployeeID
    , JobTitle
    , LastName
    , FirstName
    , ManagerID
INTO #EmployeeHierarchy
FROM EmployeeHierarchy
ORDER BY EmployeeHierarchyID
GO

然后，向表中添加一个 hierarchyid 列、在其上添加索引，然后通过加入临时表来填充它就变得相当简单了。

ALTER TABLE dbo.Employee ADD
    EmployeeHierarchyID hierarchyid NULL
GO

UPDATE Employee
SET          Employee.EmployeeHierarchyID = #EmployeeHierarchy.EmployeeHierarchyID
FROM     Employee INNER JOIN
                  #EmployeeHierarchy ON Employee.EmployeeID = #EmployeeHierarchy.EmployeeID
GO

SELECT EmployeeHierarchyID.ToString() AS EmployeeHierarchyIDString, EmployeeID, JobTitle, FirstName, LastName, ManagerID, EmployeeHierarchyID
FROM     Employee
GO

但是，请记住，如果您希望在添加 Hierarchyid 数据后保持一致，则应采用非常特定的方式来维护它。