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

Откуда:
Сообщений: 32
Здравствуйте. Возникла проблема при решении следующей задачи.
Есть таблица Детали:
- наименование;
- адрес (hierarchyid);
- количество

Данные выглядят следующим образом:
Д1              /                1
  Д2            /1/              2
    Д3          /1/1/            3
  Д4            /2/              2


Необходимо развернуть дерево, учитывая входимость всех узлов. Т.е. получить на выходе:
Д1              /
  Д2            /1/
    Д3          /1/1/
    Д3          /1/2/
    Д3          /1/3/
  Д2            /2/
    Д3          /2/1/
    Д3          /2/2/
    Д3          /2/3/
  Д4            /3/
  Д4            /4/     


Пока не совсем понятно как к решению подступиться.
27 авг 15, 19:38    [18078069]     Ответить | Цитировать Сообщить модератору
 Re: Развертывание дерева, построенного с помощью hierarchyid  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1837
Kouki,

https://msdn.microsoft.com/ru-ru/library/bb677173(v=sql.120).aspx
27 авг 15, 20:07    [18078148]     Ответить | Цитировать Сообщить модератору
 Re: Развертывание дерева, построенного с помощью hierarchyid  [new]
Kouki
Member

Откуда:
Сообщений: 32
felix_ff,

Мануалы по hierarchyid я уже изучал. Как применить их к конкретно данной задачи не совсем ясно пока.
27 авг 15, 21:01    [18078272]     Ответить | Цитировать Сообщить модератору
 Re: Развертывание дерева, построенного с помощью hierarchyid  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
declare @t table (d varchar(10), p hierarchyid, q int);
declare @n table (n int primary key);

insert into @n
values
 (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

insert into @t
values
('Д1', '/', 1),
('Д2', '/1/', 2),
('Д3', '/1/1/', 3),
('Д4', '/2/', 2);

with s as
(
 select
  t.*, cast(str(row_number() over (order by t.p)) as varchar(max)) as [path]
 from
  @t t join
  @n n on n.n <= t.q
 where
  p = hierarchyid::GetRoot()

 union all

 select
  t.*, s.[path] + str(row_number() over (order by t.p))
 from
  @t t join
  s on t.p.GetAncestor(1) = s.p join
  @n n on n.n <= t.q
)
select d, p from s order by [path];
27 авг 15, 21:13    [18078318]     Ответить | Цитировать Сообщить модератору
 Re: Развертывание дерева, построенного с помощью hierarchyid  [new]
Kouki
Member

Откуда:
Сообщений: 32
invm,

Спасибо. Буду разбираться в вашем варианте.
Только на выходе получается так:
Д1 /
Д2 /1/
Д3 /1/1/
Д3 /1/1/
Д3 /1/1/
Д2 /1/
Д3 /1/1/
Д3 /1/1/
Д3 /1/1/
Д4 /2/
Д4 /2/

Т.е. адреса не совсем корректны.
27 авг 15, 21:53    [18078481]     Ответить | Цитировать Сообщить модератору
 Re: Развертывание дерева, построенного с помощью hierarchyid  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Kouki
Т.е. адреса не совсем корректны.
with s as
(
 select
  t.*, cast('/' as varchar(max)) as [path], 1 as [level]
 from
  @t t join
  @n n on n.n <= t.q
 where
  p = hierarchyid::GetRoot()

 union all

 select
  t.*, s.[path] + str(row_number() over (order by t.p)) + '/', s.[level] + 1
 from
  @t t join
  s on t.p.GetAncestor(1) = s.p join
  @n n on n.n <= t.q
)
select space([level]) + d, replace([path], ' ', '') from s order by [path];
27 авг 15, 22:06    [18078540]     Ответить | Цитировать Сообщить модератору
 Re: Развертывание дерева, построенного с помощью hierarchyid  [new]
Kouki
Member

Откуда:
Сообщений: 32
invm,

Да, теперь все хоккей, благодарю.
С пробелами - это вы забавно придумали.
27 авг 15, 22:13    [18078561]     Ответить | Цитировать Сообщить модератору
 Re: Развертывание дерева, построенного с помощью hierarchyid  [new]
## fa diezz ##
Guest
invm,

Удивительно. Можете пояснить, почему Д3 нумеруются как 1 2 3 и 1 2 3, а не 1 2 3 и 4 5 6? По плану видно, почему. Разве такое поведение соответствует тому, что row_number() применяется к окну, которое, в данном случае, состоит из всех возвращаемых строк (а сортировка тут не важна, можно order by (select 0))?
27 авг 15, 22:36    [18078658]     Ответить | Цитировать Сообщить модератору
 Re: Развертывание дерева, построенного с помощью hierarchyid  [new]
## fa diezz ##
Guest


К сообщению приложен файл. Размер - 55Kb
27 авг 15, 22:43    [18078673]     Ответить | Цитировать Сообщить модератору
 Re: Развертывание дерева, построенного с помощью hierarchyid  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
## fa diezz ##
Разве такое поведение соответствует тому, что row_number() применяется к окну, которое, в данном случае, состоит из всех возвращаемых строк
Рекурсивная часть вызывается для каждой строки из нерекурсивной. Т.е. в рекурсивной части в s всегда одна строка.
В нерекурсивной получится две строки с Д2, что даст два вызова рекурсивной, в которой окно будет из трех строк. Итого получим 1, 2, 3 и 1, 2, 3.
27 авг 15, 23:22    [18078791]     Ответить | Цитировать Сообщить модератору
 Re: Развертывание дерева, построенного с помощью hierarchyid  [new]
## fa diezz ##
Guest
invm
## fa diezz ##
Разве такое поведение соответствует тому, что row_number() применяется к окну, которое, в данном случае, состоит из всех возвращаемых строк
Рекурсивная часть вызывается для каждой строки из нерекурсивной. Т.е. в рекурсивной части в s всегда одна строка.
В нерекурсивной получится две строки с Д2, что даст два вызова рекурсивной, в которой окно будет из трех строк. Итого получим 1, 2, 3 и 1, 2, 3.


Вот так, век живи, век учись. Всегда считал, что s будет содержать все строки предыдущего шага. Ушел гуглить описание рекурсии.
27 авг 15, 23:26    [18078811]     Ответить | Цитировать Сообщить модератору
 Re: Развертывание дерева, построенного с помощью hierarchyid  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
## fa diezz ##
а сортировка тут не важна, можно order by (select 0))
Это не так. Вот на таких данных, при сортировке по (select 0) и т.п. получим неправильный порядок:
insert into @t
values
('Д1', '/', 1),
('Д2', '/1/', 2),
('Д5', '/1/2/', 1),
('Д3', '/1/1/', 3),
('Д4', '/2/', 2);
27 авг 15, 23:32    [18078836]     Ответить | Цитировать Сообщить модератору
 Re: Развертывание дерева, построенного с помощью hierarchyid  [new]
## fa diezz ##
Guest
invm
получим неправильный порядок:[src]


Имеете ввиду визуально? Потому что структурно, order by [path] даст правильное дерево. Поидее, или мне спать пора.
27 авг 15, 23:39    [18078854]     Ответить | Цитировать Сообщить модератору
 Re: Развертывание дерева, построенного с помощью hierarchyid  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
## fa diezz ##
Имеете ввиду визуально?
Да. Потому что, согласно иерархии, Д5 идет после Д3.
27 авг 15, 23:44    [18078867]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить