生成HierarchyID

Question

我想像这样插入hierarchyId

• / -首席执行官(根)
  • /1/ -采购经理
    • /1/1/ -采购经理

- `/2/` - Sales Manager 
    - `/2/1/` - Sales Executive

这是我想要使用的层次结构，是正确的层次结构，如果是的话，我如何做到这一点，谁能给我一些代码片段。

Answer 1

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

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

用这张桌子，

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

可以使用解析使用字符串路径直接插入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

假设您有一个具有自连接(如下面所示)的表模式，并且您的首席执行官的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自动生成一组初始的层次结构值：

;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

然后，将一个层次结构列添加到表中，在其上添加一个索引，然后通过连接到temp表来填充它，这就变得非常简单了。

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

但是，请记住，如果要在添加层次结构数据后保持其一致性，则应该以非常具体的方式来维护它。