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

Откуда: Украина
Сообщений: 33
Есть таблица групп.
create table tree (id tinyint, parentID tinyint, NAME VARCHAR(20))

insert into tree (id, parentID, NAME) 
select 1, null, 'group 1'
UNION
select 2, 1, 'group 2'
UNION
select 3, 2, 'group 3'
UNION
select 4, null, 'group 4'
UNION
select 5, 4, 'group 5'
UNION
select 6, 4, 'group 6'
UNION
select 7, 6, 'group 7'

DROP TABLE tree


нужно для каждой группы получить ID ВСЕХ подчиненных групп, в том числе дочерных. Например:
ID Parent Name IDs
1 NULL group 1 2.3
2 1 group 2 3
3 2 group 3
4 NULL group 4 5.6.7
5 4 group 5
6 4 group 6 7
7 6 group 7


Заранее спасибо!
7 сен 15, 13:15    [18118247]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
Glory
Member

Откуда:
Сообщений: 104751
https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
7 сен 15, 13:22    [18118290]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
Макбет
Member

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

решается в два шага
1. рекурсией делаешь выборку дочерних элементов, см. SELECT всех подкатегорий для заданной
2. собираешь их в строку с помощью for xml path('')
7 сен 15, 14:00    [18118555]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
iap
Member

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

решается в два шага
1. рекурсией делаешь выборку дочерних элементов, см. SELECT всех подкатегорий для заданной
2. собираешь их в строку с помощью for xml path('')
Почему бы сразу внутри CTE не строить нужный список ID без всяких XML?
7 сен 15, 14:09    [18118617]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
Макбет
Member

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

можно переместить запрос на список внутрь CTE, надо смотреть по плану, где выгоднее и что требуется сделать
есть одно НО:
если использовать снаружи можно ставить любую сортировку, проводить манипуляции, скажем, получить диапазоны ID дочерних элементов и тд
если делать внутри - можно сортировать только в пределах уровня
7 сен 15, 15:47    [18119434]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
mephistofell
Member

Откуда: Украина
Сообщений: 33
Макбет, Glory,

Спасибо большое за то что пнули в правильном направлении.
Вот мое решение:

with q as (
select id, name, parentid, id as main_id from tree
union all
select c.id, c.name, c.parentid, q.main_id as main_id from tree c
inner join q on c.parentid=q.id)

SELECT  t.id, t.name, t.parentid, 
stuff((
        select ',' + CAST(u.id AS VARCHAR(20))
        from q u
        where u.main_id = t.id
        ORDER by u.main_id
        for xml path('')
    ),1,1,'') as ids
 FROM tree t ORDER BY id


Тема закрыта
7 сен 15, 15:49    [18119446]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
_human
Member

Откуда:
Сообщений: 566
iap
Почему бы сразу внутри CTE не строить нужный список ID без всяких XML?

пример, пожалуйста ? :)
7 сен 15, 15:54    [18119484]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
iap
Member

Откуда: Москва
Сообщений: 47107
_human
iap
Почему бы сразу внутри CTE не строить нужный список ID без всяких XML?

пример, пожалуйста ? :)
mephistofell
with q as (
select id, name, parentid, id as main_id from tree
union all
select c.id, c.name, c.parentid, q.main_id as main_id from tree c
inner join q on c.parentid=q.id)

SELECT  t.id, t.name, t.parentid, 
stuff((
        select ',' + CAST(u.id AS VARCHAR(20))
        from q u
        where u.main_id = t.id
        ORDER by u.main_id
        for xml path('')
    ),1,1,'') as ids
 FROM tree t ORDER BY id
with q as (
select id, name, parentid, id as main_id from tree, cast(id as varchar(max)) ids
union all
select c.id, c.name, c.parentid, q.main_id, q.ids+'.'+cast(c.id as varchar(max)) from tree c
inner join q on c.parentid=q.id)

SELECT id,name,parentid,ids
FROM tree ORDER BY id

НЕ ПРОВЕРЯЛ!!!
7 сен 15, 16:05    [18119589]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
iap
НЕ ПРОВЕРЯЛ!!!

А я проверял - не работает.
Без XML или слияния строк не обойтись хотя бы потому, что у одного родителя может быть двое или более детей - на одном цикле рекурсии на одну строку вернутся две, и их надо объединить.
7 сен 15, 16:17    [18119711]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
HierarchyID
7 сен 15, 16:21    [18119748]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
_human
Member

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

;with q as (
select id, name, parentid, id as main_id, cast(id as varchar(max)) ids from tree
union all
select c.id, c.name, c.parentid, q.main_id, q.ids+'.'+cast(c.id as varchar(max)) from tree c
inner join q on c.parentid=q.id)

SELECT id,name,parentid,ids
FROM q ORDER BY id

если исправить синтаксические ошибки, результат будет, но не тот(такой же как и у меня). Что-то типа пути к узлу. Тут нужно наоборот
7 сен 15, 16:22    [18119753]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
iap
Member

Откуда: Москва
Сообщений: 47107
Minamoto
iap
НЕ ПРОВЕРЯЛ!!!

А я проверял - не работает.
Без XML или слияния строк не обойтись хотя бы потому, что у одного родителя может быть двое или более детей - на одном цикле рекурсии на одну строку вернутся две, и их надо объединить.
Да, понял Вас. Надо ещё и id разных веток сложить. Тогда, конечно, снаружи рекурсии придётся.
7 сен 15, 16:26    [18119789]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Если расширить таблицу столбцом Path со значениями путей в виде "/1/3/8/15/22/77", то все будет решется одним простым запросом с LIKE. Например для этого пути "/1/3/8/15/22" задача найти все дочерние группы для группы "8" решается так:

SELECT * FROM tree WHERE path LIKE '/1/3/8/%'


если path группы "8" не известен, то так:

SELECT * FROM tree WHERE path LIKE (SELECT path FROM tree WHERE id=8) + '/%'


А в момент заполнения таблицы пути достаточно просто строятся:

CREATE TABLE tree (id tinyint, parentID tinyint, NAME VARCHAR(20), path VARCHAR(MAX));

INSERT INTO tree (id, parentID, NAME, path)
VALUES
	(1, null, 'group 1', '/1'),
	(2, 1, 'group 2', '/1/2'),
	(3, 2, 'group 3', '/1/2/3'),
	(4, null, 'group 4', '/4'),
	(5, 4, 'group 5', '/4/5'),
	(6, 4, 'group 6', '/4/6'),
	(7, 6, 'group 7', '/4/6/7');

DROP TABLE tree


или так:

CREATE TABLE tree (id tinyint, parentID tinyint, NAME VARCHAR(20), path VARCHAR(MAX));

INSERT INTO tree (id, parentID, NAME, path) VALUES (1, null, 'group 1', '/1');
INSERT INTO tree (id, parentID, NAME, path) VALUES (2, 1, 'group 2', (SELECT path FROM tree WHERE id = 1) + '/2');
INSERT INTO tree (id, parentID, NAME, path) VALUES (3, 2, 'group 3', (SELECT path FROM tree WHERE id = 2) + '/3');
INSERT INTO tree (id, parentID, NAME, path) VALUES (4, null, 'group 4', '/4');
INSERT INTO tree (id, parentID, NAME, path) VALUES (5, 4, 'group 5', (SELECT path FROM tree WHERE id = 4) + '/5');
INSERT INTO tree (id, parentID, NAME, path) VALUES (6, 4, 'group 6', (SELECT path FROM tree WHERE id = 4) + '/6');
INSERT INTO tree (id, parentID, NAME, path) VALUES (7, 6, 'group 7', (SELECT path FROM tree WHERE id = 6) + '/7');

DROP TABLE tree;
8 сен 15, 10:15    [18121732]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Yuri Abele
Если расширить таблицу столбцом Path со значениями путей в виде "/1/3/8/15/22/77", то все будет решется одним простым запросом с LIKE. Например для этого пути "/1/3/8/15/22" задача найти все дочерние группы для группы "8" решается так:


Можно, но зачем сочинать велосипед, если он уже сочинен майкрософтом: HierarchyID?
8 сен 15, 12:12    [18122376]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
Макбет
Member

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

честно говоря с HierarchyID не работал, но судя по статье в msdn это лишь красивый способ хранения иерархических данных, к тому же с ограничениями по числу уровней (максимум 892 байта)
приведите пожалуйста пример запроса как можно найти все дочерние элементы конкретного узла, независимо от уровня?
8 сен 15, 13:22    [18122778]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Winnipuh
Можно, но зачем сочинать велосипед, если он уже сочинен майкрософтом: HierarchyID?

Я уже в другом топике писал почему - потому, что HierarchyID-это .NET тип, который при вызове его методов:
1. вынужден сериализовать/десериализовывать значения из таблицы
2. как-то обрабатывать эти значения рекурсивно

Поэтому на ваш "можно" мой - да, можно, но зачем, если есть возможность его обойти меньшей кровью?
Вот когда простых средств не хватит (мне как-то всегда хватало), тогда можно и поизвращаться
8 сен 15, 15:04    [18123425]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Макбет,
в "моем" варианте я уже показал как (см. выше):

SELECT * FROM tree WHERE path LIKE '/1/3/8/%'
8 сен 15, 15:07    [18123436]     Ответить | Цитировать Сообщить модератору
 Re: Как получить ID всех подчиненных элементов в дереве?  [new]
iap
Member

Откуда: Москва
Сообщений: 47107
Yuri Abele
Макбет,
в "моем" варианте я уже показал как (см. выше):

SELECT * FROM tree WHERE path LIKE '/1/3/8/%'
Требовалось собрать все ID для каждого корня в строке через точку.
А у вас что?
8 сен 15, 16:25    [18124044]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить