Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Иерархический CTE: как для каждого уровня иерархии вывести id корня иерархии?  [new]
WinterGraveyard
Member

Откуда:
Сообщений: 72
Сейчас решил эту проблему введением функции, которая от заданного узла поднимается вверх по иерархии до корня, и вызовом этой функции для каждой записи, возвращаемой иерархическим CTE:
use tempdb
go
if object_id('tempdb..test') is not null
  drop table dbo.test;
create table dbo.test (
  id int not null,
  parent int not null,
  name varchar(10)
);
insert into dbo.test values
  (1, 0, 'A'),
  (2, 0, 'B'),
  (3, 1, 'AA'),
  (4, 3, 'AAA'),
  (5, 2, 'BA'),
  (6, 5, 'BAA'),
  (7, 2, 'BB'),
  (8, 7, 'BBA');

if object_id('tempdb..uf_get_root') is not null
  drop function dbo.uf_get_root;
go
create function dbo.uf_get_root(@id int)
returns table as
return
with cte as (
  select id, parent from dbo.test where id=@id
  union all
  select
    t.id, t.parent
  from cte e
  join dbo.test t on t.id=e.parent
)
select top 1 id from cte order by id;
go

;with cte as (
  select id, parent, name, 0 level, convert(varchar(max), id) path
  from dbo.test where parent=0
  union all
  select
    t.id, t.parent, t.name, e.level+1, convert(varchar(max), e.path+'/'+convert(varchar, t.id))
  from cte e
  join dbo.test t on t.parent=e.id
)
select
  e.id, e.parent, f.id root, e.name, e.level, e.path
from cte e
cross apply dbo.uf_get_root(e.id) f
order by e.path

drop table dbo.test;
drop function dbo.uf_get_root;

а можно ли как-то обойтись без функции uf_get_root - т.е. всё сделать только одним запросом?
20 авг 15, 08:28    [18043686]     Ответить | Цитировать Сообщить модератору
 Re: Иерархический CTE: как для каждого уровня иерархии вывести id корня иерархии?  [new]
WinterGraveyard
Member

Откуда:
Сообщений: 72
Небольшое дополнение: в реальной ситуации возможен случай, когда id дочерней записи меньше id родительской записи, поэтому функция uf_get_root должна выглядеть так:
create function dbo.uf_get_root(@id int)
returns table as
return
with cte as (
  select id, parent, 0 level from dbo.test where id=@id
  union all
  select
    t.id, t.parent, e.level+1
  from cte e
  join dbo.test t on t.id=e.parent
)
select top 1 id from cte order by level desc;
20 авг 15, 08:36    [18043704]     Ответить | Цитировать Сообщить модератору
 Re: Иерархический CTE: как для каждого уровня иерархии вывести id корня иерархии?  [new]
Гхостик
Guest
with cte as (
  select id root_id, id, parent, name, 0 level, convert(varchar(max), id) path
  from dbo.test where parent=0
  union all
  select
    e.root_id, t.id, t.parent, t.name, e.level+1, convert(varchar(max), e.path+'/'+convert(varchar, t.id))
  from cte e
  join dbo.test t on t.parent=e.id
)
20 авг 15, 08:38    [18043709]     Ответить | Цитировать Сообщить модератору
 Re: Иерархический CTE: как для каждого уровня иерархии вывести id корня иерархии?  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
;with cte as (
  select id, parent, name, 0 level, convert(varchar(max), id) path, id as root
  from dbo.test where parent=0
  union all
  select
    t.id, t.parent, t.name, e.level+1, convert(varchar(max), e.path+'/'+convert(varchar, t.id)), e.root
  from cte e
  join dbo.test t on t.parent=e.id
)
select
  e.id, e.parent, e.root, e.name, e.level, e.path
from cte e
order by e.path
20 авг 15, 08:38    [18043712]     Ответить | Цитировать Сообщить модератору
 Re: Иерархический CTE: как для каждого уровня иерархии вывести id корня иерархии?  [new]
WinterGraveyard
Member

Откуда:
Сообщений: 72
Knyazev Alexey,

как всё просто оказалось.. Спасибо!
20 авг 15, 09:19    [18043847]     Ответить | Цитировать Сообщить модератору
 Re: Иерархический CTE: как для каждого уровня иерархии вывести id корня иерархии?  [new]
Mike_za
Member

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

А вам всегда нужно полностью всю таблицу возвращать будет?
20 авг 15, 10:37    [18044261]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить