生成HierarchyID

Question

生成HierarchyID

sqlsql-server-2008hierarchicalhierarchyid

9

我想要按照以下方式插入hierarchyId：

/ - CEO (根节点)
- /1/ - 采购经理
  - /1/1/ - 采购执行员
- /2/ - 销售经理
  - /2/1/ - 销售执行员

这就是我想要使用的层次结构，如果正确的话，我该如何实现它？有人可以给我一些代码片段吗？

- Mohanavel

请检查以下链接：使用 hierarchyid 数据类型（数据库引擎）教程：使用 hierarchyid 数据类型 - Mr. TA

2个回答

-1

假设您有一个带有自连接的表模式（如下所示），并且您的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

然后，将层次结构 ID 列添加到表中，为其添加索引，然后通过与临时表连接来填充它变得相当简单。

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

然而，请记住，如果您希望在添加后保持层次结构数据的一致性，则应以非常特定的方式进行维护。

- JamieSee

这是一个关于 Hid 生成的例子，当你已经有了 Id-ParentId 列时可以使用。https://www.codeproject.com/Articles/1192607/Combination-of-Id-ParentId-and-HierarchyId - Vadim Loboda

我不确定是否只有我，但上面的查询中有很多语法错误。例如，在第二个查询中，“Employees”表被引用为“Employee”。 - luizs81

@luizs81 呜呜。没有实际的语法错误，但是名字等方面有太多错误了。感谢指出。我已经清理干净并在实际测试数据库上运行验证了。* 打脸 * - JamieSee

@JamieSee 没问题。感谢你修复它。我尝试自己更新你的帖子，但不知何故它不允许我这样做。无论有什么错误，在我理解代码意图后，我都能达到我想要的效果。 - luizs81

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jeff Ogata · Accepted Answer

我在搜索有关层次结构数据类型的信息时遇到了这个问题，并认为对于其他人来说，将代码插入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')

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