Sql HierarchyId Как получить последних потомков?

Используя 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/

Какой запрос даст мне Джона и Криса?


sql sql-server hierarchyid
person Eric    schedule 19.12.2011    source источник

Ответы (3)


arrow_upward
13
arrow_downward

Возможно, есть лучшие способы, но это, похоже, делает работу.

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

Обновление от 22 мая 2012 г.

Запрос выше завершится ошибкой, если номера узлов не находятся в непрерывной последовательности. Вот еще одна версия, которая должна позаботиться об этом.

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)
person Mikael Eriksson    schedule 19.12.2011

arrow_upward
1
arrow_downward

Поскольку вам нужны только листья, и вам не нужно получать их от определенного предка, простой нерекурсивный запрос, подобный этому, должен выполнить эту работу:

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 в условии.

person Branko Dimitrijevic    schedule 19.12.2011
comment
Я совершенно уверен, что OP использует тип данных HierarchyID SQL Server 2008, что объясняет необычное представление (см. msdn.microsoft.com/en-us/magazine/cc794278.aspx). - person Daniel Pratt; 19.12.2011
comment
@DanielPratt Ааа ... Теперь я вижу, что вопрос был повторно помечен как [sql-server]. - person Branko Dimitrijevic; 19.12.2011
comment
Спасибо за ответ, Бранко, но в моем примере поле Id является целым числом, а HierarchyId является sql HierarchyId, поэтому их нельзя сравнивать. Вы говорите, что мне нужно изменить ключ таблицы на иерархический идентификатор? - person Eric; 19.12.2011
comment
@EricNeifert Я предполагал, что вы используете классический дизайн для представления иерархических данных в реляционных базах данных, а не механизм, специфичный для MS SQL Server. К сожалению, я недостаточно знаком с механизмом, специфичным для MS SQL Server, чтобы рекомендовать, действительно ли вам следует переключиться, но кажется, что ту же основную идею можно применить и к MS SQL Server — см. Ответ Марка Баннистера. - person Branko Dimitrijevic; 19.12.2011

arrow_upward
1
arrow_downward

Привет, я использую этот и отлично работает для меня.

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
person SimonOzturk    schedule 02.03.2015