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

Откуда:
Сообщений: 159
Добрый день.
Как реализовать следующую задачу:
есть иерархия группа-пользователь, у групп и пользователей есть роли. Пользователь или группа наследуют роли родителя.
Мне надо для каждого члена иерархии вывести его роли+наследуемые роли так, чтоб каждая роль была в отдельной строке (читабельней получается отчет).
Создавать объекты (процедуры) нельзя.

К сообщению приложен файл. Размер - 29Kb
4 фев 14, 14:16    [15516508]     Ответить | Цитировать Сообщить модератору
 Re: Реккурсивный запрос с увеличением кол-ва строк в результирующей таблице  [new]
PA
Member

Откуда: Санкт-Петербург
Сообщений: 4146
Dika!,

У вас какие-то противоречивые исходные данные.
Обычно на верхнем уровне иерархии находится элемент с пустым родителем.
Т.е. если бы первая строка такая была,
rootparentchildroles
group1nullgroup1role1

то обычный CTE легко справился.
А в вашем случае эта строка из общей логики запроса выпадает.
4 фев 14, 17:01    [15518012]     Ответить | Цитировать Сообщить модератору
 Re: Реккурсивный запрос с увеличением кол-ва строк в результирующей таблице  [new]
Dika!
Member

Откуда:
Сообщений: 159
Исходная таблица создается из 2-х таблиц.
Вот придумала код ,если брать "совсем" исходные данные
with temp_table as 
(
select 'x1' as parent, 'x2' as child
union all
select 'x2' as parent, 'x3' as child
union all
select 'x3' as parent, 'x4' as child
), 

rec1 as
(
select parent, parent as "child" from temp_table
union all
select r.parent, t.child from temp_table t
 inner join rec1 r on r.child=t.parent
union all
select t.child, t.child from temp_table t
 inner join rec1 r on r.child=t.parent
)

select distinct * from rec1


в результате получается таблица, потом соединить с ролями и то что надо. Смущает distinct, после рекурсии получаются лишние записи. Можно улучшить?

К сообщению приложен файл. Размер - 8Kb
4 фев 14, 17:27    [15518162]     Ответить | Цитировать Сообщить модератору
 Re: Реккурсивный запрос с увеличением кол-ва строк в результирующей таблице  [new]
Добрый Э - Эх
Guest
а зачем ты дважды юнионишь данные? для раскрутки твоей рекурсии достаточно одного юниона, где в первой его части будут якорные элементы, во второй - рекурсивный переход. Единственное, что нужно чутко определиться с тем, какие элементы являются корнями существующих деревьев
4 фев 14, 18:02    [15518421]     Ответить | Цитировать Сообщить модератору
 Re: Реккурсивный запрос с увеличением кол-ва строк в результирующей таблице  [new]
Добрый Э - Эх
Guest
и, кстати, совершенно непонятно почему ты считаешь допустимыми элементы иерархии такую пару, как (x1, x1), к примеру.
4 фев 14, 18:05    [15518445]     Ответить | Цитировать Сообщить модератору
 Re: Реккурсивный запрос с увеличением кол-ва строк в результирующей таблице  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Только вот ето
with temp_table as 
(
select 'x1' as parent, 'x2' as child
union all
select 'x2' as parent, 'x3' as child
union all
select 'x3' as parent, 'x4' as child
), 

совершенно не тоже самое,что в стартовом посте
4 фев 14, 18:18    [15518537]     Ответить | Цитировать Сообщить модератору
 Re: Реккурсивный запрос с увеличением кол-ва строк в результирующей таблице  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Dika!,

Может это поможет Вам?
4 фев 14, 18:22    [15518577]     Ответить | Цитировать Сообщить модератору
 Re: Реккурсивный запрос с увеличением кол-ва строк в результирующей таблице  [new]
Dika!
Member

Откуда:
Сообщений: 159
Добрый Э - Эх
и, кстати, совершенно непонятно почему ты считаешь допустимыми элементы иерархии такую пару, как (x1, x1), к примеру.

потому что таблицу такого вида удобно джоинить с ролями по полю Parent и если отфильтровать по полю Child, то получу все роли этого child`а + наследуемые от его родителей.

Добрый Э - Эх
а зачем ты дважды юнионишь данные? для раскрутки твоей рекурсии достаточно одного юниона, где в первой его части будут якорные элементы, во второй - рекурсивный переход. Единственное, что нужно чутко определиться с тем, какие элементы являются корнями существующих деревьев

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

Maxx - вы правы, в первом посте я выложила таблицу после рекурсии, в 3-м исходная.
5 фев 14, 08:27    [15520337]     Ответить | Цитировать Сообщить модератору
 Re: Реккурсивный запрос с увеличением кол-ва строк в результирующей таблице  [new]
Добрый Э - Эх
Guest
но я так и не понял - основываясь ан каких таких иерархических связях должна была родиться строка (x1,x1)?
В исходной таблице не указано ссылки элемента самого на себя...
5 фев 14, 09:04    [15520426]     Ответить | Цитировать Сообщить модератору
 Re: Реккурсивный запрос с увеличением кол-ва строк в результирующей таблице  [new]
Dika!
Member

Откуда:
Сообщений: 159
Добрый Э - Эх,
не дописала условие в так называемом "якоре"

select parent, parent as "child" from temp_table [u]where parent='x1'[/u]


изменила рекурсию,
with temp_table as 
(
select 'x1' as parent, 'x2' as child
union all
select 'x2' as parent, 'x3' as child
union all
select 'x3' as parent, 'x4' as child
), 

rec1 as
(
select parent, parent as "child" from temp_table --where parent='x1'
union all
select r.parent, t.child from temp_table t
 inner join rec1 r on r.child=t.parent
)


select  * from rec1


но здесь не хватает последней строки "child", "child" для потомка последнего уровня "x4".
Похоже на "гадание на кофейной гуще".
Статьи про рекурсию читала, а самой проследить алгоритм формирования таблицы rec1 не получается

К сообщению приложен файл. Размер - 7Kb
5 фев 14, 09:30    [15520502]     Ответить | Цитировать Сообщить модератору
 Re: Реккурсивный запрос с увеличением кол-ва строк в результирующей таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Dika!,

добавьте в CTE отладочных полей - номер итерации рекурсии (level),
путь по дереву (список узлов через разделитель) и т.п.
Станет намного яснее. В конце отладки эти поля удалите.

Ведь результат запроса из рекурсивного CTE содержит записи для всех уровней.
А Вам нужен последний, насколько я понимаю.
5 фев 14, 09:41    [15520527]     Ответить | Цитировать Сообщить модератору
 Re: Реккурсивный запрос с увеличением кол-ва строк в результирующей таблице  [new]
Dika!
Member

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

добавьте в CTE отладочных полей - номер итерации рекурсии (level),
путь по дереву (список узлов через разделитель) и т.п.
Станет намного яснее. В конце отладки эти поля удалите.
вроде разобралась

iap
Ведь результат запроса из рекурсивного CTE содержит записи для всех уровней.
А Вам нужен последний, насколько я понимаю.
не совсем так, все строки нужны, чтобы можно было посмотреть роли любого элемента из иерархии
5 фев 14, 10:03    [15520631]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить