Sql HierarchyId 如何获取最后的后代？

Question

使用t-sql hierarchy我如何获取所有没有子项的行(即最后的后代)？

假设我的表格结构如下:

 Id, 
 Name,
 HierarchyId

并有这些行:

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

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

什么查询会给我约翰和克里斯？

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)