Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Поиск корней по листьям при помощи СТЕ  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Есть иерархический справочник

IF OBJECT_ID('tempdb..#SimpleTree') > 0 DROP TABLE #SimpleTree
CREATE TABLE  #SimpleTree (Id Int, ParentId Int)  

INSERT INTO #simpleTree SELECT 1, null /* корень*/
INSERT INTO #simpleTree SELECT 2, 1
INSERT INTO #simpleTree SELECT 3, 2
INSERT INTO #simpleTree SELECT 4, 2
INSERT INTO #simpleTree SELECT 5, 4
INSERT INTO #simpleTree SELECT 6, null /* корень*/
INSERT INTO #simpleTree SELECT 7, 6
INSERT INTO #simpleTree SELECT 8, 7
INSERT INTO #simpleTree SELECT 9, 8


Задача получить листы и корни

/* Что нужно
Id_______RootId 
2____________1
3____________1
4____________1
5____________1
7____________6
8____________6
9____________6
*/


Сделал так

;WITH CTE (Id,  ParentId , R ) AS (
  SELECT Id, ParentID , 1  FROM #SimpleTree WHERE ParentId IS NOT NULL  
  UNION ALL 
  SELECT m.Id, k.parentId, R+1  FROM CTE  k inner join #SimpleTree m on k.id = m.ParentId
) 

SELECT Id, [RootId] = ParentId FROM CTE Z  
WHERE Z.R = (SELECT MAX(C.R) FROM CTE C WHERE C.Id = Z.Id) 
ORDER BY Id



Но мучает мысль, что есть вариант более изящный, без глубины вложения и
WHERE Z.R = (SELECT MAX(C.R) FROM CTE C WHERE C.Id = Z.Id)  


Есть ли действительно более просто решение с СТЕ? Есть ли решение, выигрывающее у этого по быстродействию?
10 апр 13, 16:07    [14162797]     Ответить | Цитировать Сообщить модератору
 Re: Поиск корней по листьям при помощи СТЕ  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
;WITH CTE (Id,  ParentId) AS (
  SELECT Id, Id FROM #SimpleTree WHERE ParentId IS NULL  
  UNION ALL 
  SELECT m.Id, k.parentId
  FROM CTE  k inner join #SimpleTree m on k.id = m.ParentId
) 
SELECT Id, [RootId] = ParentId FROM CTE Z  
WHERE Id != ParentId
ORDER BY Id
?
10 апр 13, 16:12    [14162830]     Ответить | Цитировать Сообщить модератору
 Re: Поиск корней по листьям при помощи СТЕ  [new]
iap
Member

Откуда: Москва
Сообщений: 47107
Cammomile,

а ничего, что этот запрос сразу отсекает корни, которые одновременно и листья?
10 апр 13, 16:12    [14162832]     Ответить | Цитировать Сообщить модератору
 Re: Поиск корней по листьям при помощи СТЕ  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Паганель, а и правда! Стыдно-то как =(

iap, да, в данном случае это особенность бизнеса. Корень - некая агрегация в справочнике, все остальное -- структура персонала по которой надо строить отчетность.
10 апр 13, 16:18    [14162879]     Ответить | Цитировать Сообщить модератору
 Re: Поиск корней по листьям при помощи СТЕ  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
;WITH CTE (Id,  ParentId , R ) AS (
  SELECT Id, id , 1  FROM #SimpleTree WHERE ParentId IS  NULL  
  UNION ALL 
  SELECT m.Id, k.parentId, R+1  FROM CTE  k inner join #SimpleTree m on k.id = m.ParentId
) 

SELECT Id, [RootId] = ParentId FROM CTE Z  
where R>1 -- это если не надо корневые узлы получать. Если надо то убрать

ORDER BY Id
10 апр 13, 16:22    [14162908]     Ответить | Цитировать Сообщить модератору
 Re: Поиск корней по листьям при помощи СТЕ  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Вапче отлично, спасибо!
10 апр 13, 16:42    [14163080]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить