Sql HierarchyId如何获取最后的子节点？

Question

使用 t-sql 的 层次结构 ID，如何获取所有没有子项的行（即最后一级后代）？

假设我的表格结构如下：

 Id, 
 Name,
 HierarchyId

并且有这些行：

1, Craig, /
2, Steve, /1/
3, John, /1/1/

4, Sam, /2/
5, Matt, /2/1/
6, Chris, /2/1/1/

什么查询可以给我John和Chris？

- Eric

Answer 1

也许有更好的方法，但是这种方式似乎能够完成任务。

declare @T table
(
  ID int,
  Name varchar(10),
  HID HierarchyID
)

insert into @T values
(1, 'Craig', '/'),
(2, 'Steve', '/1/'),
(3, 'John', '/1/1/'),
(4, 'Sam', '/2/'),
(5, 'Matt', '/2/1/'),
(6, 'Chris', '/2/1/1/')

select *
from @T
where HID.GetDescendant(null, null) not in (select HID 
                                            from @T)

结果：

ID          Name       HID
----------- ---------- ---------------------
3           John       0x5AC0
6           Chris      0x6AD6

更新 2012-05-22

如果节点编号不是连续的序列，上面的查询将失败。以下是另一个版本，应该可以解决这个问题。

declare @T table
(
  ID int,
  Name varchar(10),
  HID HierarchyID
)

insert into @T values
(1, 'Craig', '/'),
(2, 'Steve', '/1/'),
(3, 'John', '/1/1/'),
(4, 'Sam', '/2/'),
(5, 'Matt', '/2/1/'),
(6, 'Chris', '/2/1/2/') -- HID for this row is changed compared to above query

select *
from @T
where HID not in (select HID.GetAncestor(1)
                  from @T
                  where HID.GetAncestor(1) is not null)

- Mikael Eriksson

Answer 2

由于您只需要叶子节点，而且不需要从特定的祖先获取它们，因此像这样的简单非递归查询应该可以完成任务：

SELECT * FROM YOUR_TABLE PARENT
WHERE
    NOT EXISTS (
        SELECT * FROM YOUR_TABLE CHILD
        WHERE CHILD.HierarchyId = PARENT.Id
    )

以简单易懂的语言来说，选择没有子行的每一行。

这里假设你的HierarchyId是指向Id的外键而不是像你的示例中呈现出的整个“路径”。如果不是这样，那么这很可能是你应该在数据库模型中首先修复的问题。

---编辑---

好的，这是实际可用的MS SQL Server特定查询：

SELECT * FROM YOUR_TABLE PARENT
WHERE
    NOT EXISTS (
        SELECT * FROM YOUR_TABLE CHILD
        WHERE
            CHILD.Id <> PARENT.Id
            AND CHILD.HierarchyId.IsDescendantOf(PARENT.HierarchyId) = 1
    )

请注意，IsDescendantOf将任何行视为其自身的后代，因此我们还需要在条件中添加CHILD.Id <> PARENT.Id。这一点需要特别留意。

- Branko Dimitrijevic

---

我相当确定OP正在使用SQL Server 2008的HierarchyID数据类型，这解释了不寻常的表示方式（请参见http://msdn.microsoft.com/en-us/magazine/cc794278.aspx）。 - Daniel Pratt

@DanielPratt 啊...我现在明白了，这个问题已经被重新标记为[sql-server]。 - Branko Dimitrijevic

谢谢您的回复，Branko。但是在我的例子中，Id字段是一个整数，而HierarchyId是一个SQL HierarchyId，因此它们不能进行比较。您是说我需要将表的键更改为HierarchyId吗？ - Eric

@EricNeifert 我假设您正在使用传统的设计来表示关系型数据库中的分层数据，而不是 MS SQL Server 特定的机制。不幸的是，我对 MS SQL Server 特定机制的了解还不够深入，无法建议您是否应该进行转换，但似乎可以将相同的基本思想应用于 MS SQL Server - 请参见 Mark Bannister 的答案。 - Branko Dimitrijevic

Answer 3

嗨，我使用这个程序，对我来说完美无缺。

CREATE TABLE [dbo].[Test]([Id] [hierarchyid] NOT NULL,  [Name] [nvarchar](50) NULL)
DECLARE @Parent AS HierarchyID = CAST('/2/1/' AS HierarchyID) -- Get Current Parent
DECLARE @Last AS HierarchyID
SELECT @Last = MAX(Id) FROM Test WHERE Id.GetAncestor(1) = @Parent -- Find Last Id for this Parent

INSERT INTO Test(Id,Name) VALUES(@Parent.GetDescendant(@Last, NULL),'Sydney') -- Insert after Last Id

- SimonOzturk