Рекурсия SQL Server с несколькими таблицами
Я пытаюсь привлечь пользователей, входящих в группы, но группы также могут быть членами групп. Таким образом, вопрос в том, кто имеет «эффективный» доступ к определенным группам. Я видел много примеров использования CTE на SQL Server, и это похоже на один и тот же случай, но все примеры довольно просты и касаются одной таблицы, такой как организационная диаграмма. В моем случае мне нужно объединить несколько таблиц, и я просто не могу понять всю суть рекурсии.
Таблицы:
usergroup: у каких пользователей какие группы
uid intgid int
groupgroup: какие группы дают какие группы
parentid intchildid int
group: многие группы в одной sourceid.
gid intsourceid intname varchar
Например:
usergroup
uid gid
------------
1 101
1 102
2 102
2 120
groupgroup
parentid childid
-------------------
103 101
110 103
120 110
group
gid sourceid name
-----------------------
101 5 group101
102 5 group102
103 5 group103
110 8 group110
120 8 group120
Разница между родительским и дочерним идентификатором заключается в том, что родительский идентификатор предоставляет дочерний идентификатор. Итак, если вы являетесь членом родительского идентификатора, у вас есть эффективный доступ к childid. Как и ожидалось, родительский и дочерний идентификаторы БУДУТ СУЩЕСТВОВАТЬ в групповой таблице.
Моя цель — просто получить список всех пользователей, которые имеют доступ к тем или иным группам в данном источнике (идентификатор источника). Без вложенности для sourceid 5 было бы просто:
select *
from usergroup
inner join group on group.gid = usergroup.gid
where group.sourceid = 5
И этот запрос даст мне, что у пользователя 1 есть группы 101, 102, а у пользователя 2 — 102.
Но проблема в том, что у пользователя может быть родительский gid, который дает дочерний идентификатор на любом уровне вложенности, и эти дочерние элементы могут быть gid с sourceid, равным 5.
Вместо этого я бы надеялся, что у пользователя 1 все еще есть 101 и 102 напрямую, а у пользователя 2 есть 102 напрямую И 101 и 103 из-за вложенности.
На базовом уровне мне просто нужно знать, что пользователи XYS имеют группы ABC в источнике либо напрямую, либо через вложение и флаг или другой маркер, если это вложение или прямой. Если бы мы могли получить что-то вроде уровня (0 = прямой, 1 = на один уровень выше и т. д.), это было бы еще лучше.
Я могу сделать это в коде Java, создав большую карту/список и зациклившись, но есть ли элегантный способ получить это напрямую из SQL Server и, возможно, быстрее, чем в коде?
Спасибо!
Я не вижу, чем это отличается от любых других вопросов о рекурсивном cte, сегодня было задано несколько вопросов, посвященных этой проблеме stackoverflow.com/questions/78866869/… просто следуйте родительской группеID и т. д.
Подсказка: используйте примеры CTE, которые вы видите повсюду в таблице groupgroup, чтобы создать проекцию, которая возвращает строку для каждого потомка (как childid) каждого предка (как parentid). Затем замените usergroup в своем запросе на этот CTE.
Ответы 2
Рекурсивный CTE состоит из привязки запроса, за которым следует UNION ALL к одному или нескольким рекурсивным запросам. (Обычно существует только одна рекурсивная часть, но в некоторых сценариях, например в двоичных деревьях, их может быть более одной.)
В вашем случае якорем будет выбрано все прямое членство в группах из таблицы UserGroup. Эта часть представляет собой обычный запрос, который заполняет рекурсивный CTE начальным набором строк.
Рекурсивная часть запроса расширяет существующие строки с помощью дополнительного запроса (или запросов), который объединяет существующие строки со всем, что необходимо для рекурсивного расширения результатов. В вашем случае он соединит CTE с таблицей GroupGroup, чтобы пройти по цепочке и включить все родительские группы.
Для каждой строки исходного набора рекурсивный запрос может возвращать ноль, одну или несколько дополнительных строк. Эти результаты добавляются в объединенный набор результатов и возвращаются в рекурсию, что, возможно, дает еще больше результатов. Хотя должен быть предел. ПО умолчанию SQL Server допускает 100 уровней или рекурсию, но это можно переопределить с помощью параметра MAXRECURSION.
with ExpandedUserGroups as (
-- Anchor part
select ug.uid, ug.gid
from UserGroup ug
union all
-- Recursive part
select xug.uid, gg.parentid
from ExpandedUserGroups xug
join GroupGroup gg
on gg.childid = xug.gid
)
select xug.uid, xug.gid, g.sourceid, g.name
from ExpandedUserGroups xug
join [Group] g
on g.gid = xug.gid
order by xug.uid, xug.gid;
Результаты
См. эту db<>fiddle для демонстрации.
Вот и все, спасибо. Я изменил скрипт, чтобы переключить родительский и дочерний идентификатор, добавить уровень и предложениеwhere. Но это было ключом, спасибо!
Небольшая модификация приведенного выше переключения родительского и дочернего элементов, добавление уровня вложенности и предложенияwhere, но именно это объединение было ключевым.
with ExpandedUserGroups as (
-- Anchor part
select ug.uid, ug.gid, 0 as level
from UserGroup ug
union all
-- Recursive part
select xug.uid, gg.childid, level+1 as level
from ExpandedUserGroups xug
join GroupGroup gg
on gg.parentid = xug.gid
)
select xug.uid, xug.gid, xug.level, g.sourceid
from ExpandedUserGroups xug
join [Group] g
on g.gid = xug.gid
where sourceid = 5
order by xug.uid, xug.gid;
Пожалуйста, предоставьте минимально воспроизводимый пример с выборочными данными (охватывающими все крайние случаи) и желаемыми результатами.