Рекурсивный запрос - Только выбирает узлы, где вершины представляют активные данные
Учитывая следующий рекурсивный запрос:
WITH DepartmentHierarchy (DepartmentID, Name, IsInactive, IsSpecial, ParentId, HierarchyLevel) AS
(
-- Base case
SELECT
DepartmentId,
Name,
IsInactive,
IsSpecial,
ParentId,
1 as HierarchyLevel
FROM StoreDepartment
WHERE ParentId IS NULL
UNION ALL
-- Recursive step
SELECT
d.DepartmentId,
d.Name,
d.IsInactive,
d.IsSpecial,
d.ParentId,
dh.HierarchyLevel + 1 AS HierarchyLevel
FROM StoreDepartment d
INNER JOIN DepartmentHierarchy dh ON
d.ParentId = dh.DepartmentId
) SELECT * FROM DepartmentHierarchy
Я могу выбрать данные, которые похожи на это:
DepartmentId, Name, IsInactive, IsSpecial, ParentId, HeirarchyLevel
1, Store, 0, 0, NULL, 1
2, Main Department 1, 0, 1, 2
3, Main Department 2, 0, 1, 2
4, Sub For Main 1, 0, 2, 3
Кроме того, предположите, что таблица существует с DepartmentId и ItemId (исключая: DepartmentItemRelationship). Вершины от иерархии отдела соединяются с объектами здесь.
Я хочу, чтобы мой рекурсивный запрос только возвратил узлы (на любом уровне), которые имеют по крайней мере одну вершину ниже их с соответствием в таблице отношений отдела/объекта. Эти узлы могли быть 6, или 7 уравнивает, таким образом, я не уверен, как я исправил бы свой запрос, чтобы убедиться включать их.
Спасибо, Kyle
2 ответа
Вы можете создать столбец пути, который отслеживает иерархию. Затем вы можете добавлять только те дочерние узлы, которые совпадают в таблице DepartmentItemRelationship. И, наконец, получить только те узлы, у которых есть хотя бы дочерний элемент.
Попробуйте что-то вроде этого:
WITH DepartmentHierarchy (DepartmentID, Name, IsInactive, IsSpecial, ParentId, HierarchyLevel) AS
(
-- Base case
SELECT
'/'+cast( DepartmentId as varchar(max)) as [path]
DepartmentId,
Name,
IsInactive,
IsSpecial,
ParentId,
1 as HierarchyLevel
FROM StoreDepartment
WHERE ParentId IS NULL
UNION ALL
-- Recursive step
SELECT
dh.[path] +'/'+ cast( d.DepartmentId as varchar(max)) as [path]
d.DepartmentId,
d.Name,
d.IsInactive,
d.IsSpecial,
d.ParentId,
dh.HierarchyLevel + 1 AS HierarchyLevel
FROM StoreDepartment d
INNER JOIN DepartmentHierarchy dh ON
d.ParentId = dh.DepartmentId
where exists ( select top 1 1
from DepartmentItemRelationship di
where di.DepartmentId = d.DepartmentId )
)
SELECT *
FROM DepartmentHierarchy dh
where exists ( select top 1 1
from DepartmentHierarchy
where charindex('/'+dh.DepartmentID+'/',[path]) > 0)
Если я правильно вас понял, вам нужны все узлы, которые находятся ровно на один уровень выше конечного уровня?
На самом деле вам не нужен рекурсивный запрос за это. Все, что вам нужно, это сначала найти листовые узлы, а затем выбрать всех родителей.
WITH LeafNodeParents AS
(
SELECT DISTINCT ParentId
FROM StoreDepartment
WHERE DepartmentId NOT IN
(
SELECT DISTINCT ParentId FROM StoreDepartment
)
)
SELECT d.DepartmentId, d.Name, d.IsInactive, d.IsSpecial, d.ParentId
FROM LeafNodeParents p
INNER JOIN StoreDepartment d
ON d.DepartmentId = p.ParentId
Единственное, о чем это вам не скажет, так это об уровне. Я не уверен, насколько сильно вам это нужно. Если вы этого не сделаете, это должно работать лучше, чем рекурсивная версия; если вы это сделаете, похоже, что запрос Хосе подходит для этого (судя по беглому взгляду).