SQL Server: запрос иерархических и ссылочных данных
Я работаю над базой данных активов, которая имеет иерархию. Кроме того, существует таблица "ReferenceAsset", которая фактически указывает на актив. Базовый актив в основном работает как переопределение, но он выбирается так, как если бы он был уникальным новым активом. Одним из переопределений, которое устанавливается, является parent_id.
Столбцы, имеющие отношение к выбору иерархии:
Актив: id (основной), parent_id
Ссылка на актив: id (основной), asset_id (foreignkey->Asset), parent_id (всегда Asset)
--- ИЗДАН 5/27----
Образец данных таблицы восстановления (после объединений):
id | asset_id | name | parent_id | milestone | type 3 3 suit null march shape 4 4 suit_banker 3 april texture 5 5 tie null march shape 6 6 tie_red 5 march texture 7 7 tie_diamond 5 june texture -5 6 tie_red 4 march texture
id < 0 (как и в последней строке) означает активы, на которые имеются ссылки. У ссылочных активов есть несколько столбцов, которые переопределены (в этом случае важен только parent_id).
Ожидается, что, если я выберу все активы с апреля, я должен сделать вторичный выбор, чтобы получить все ветви дерева соответствующего запроса:
поэтому вначале совпадение запроса приведет к:
4 4 suit_banker 3 april texture
Затем после CTE мы получаем полную иерархию, и наш результат должен быть таким (пока это работает)
3 3 suit null march shape 4 4 suit_banker 3 april texture -5 6 tie_red 4 march texture
и вы видите, родительский элемент id:-5 есть, но чего не хватает, что необходимо, так это ссылочного актива и родителя ссылочного актива:
5 5 tie null march shape 6 6 tie_red 5 march texture
В настоящее время мое решение работает для этого, но оно ограничено только одной глубиной ссылок (и я чувствую, что реализация довольно некрасива).
--- Отредактировано ---- Вот моя основная функция выбора. Это должно лучше продемонстрировать, в чем заключается реальное осложнение: AssetReference.
Select A.id as id, A.id as asset_id, A.name,A.parent_id as parent_id, A.subPath, T.name as typeName, A2.name as parent_name, B.name as batchName,
L.name as locationName,AO.owner_name as ownerName, T.id as typeID,
M.name as milestoneName, A.deleted as bDeleted, 0 as reference, W.phase_name, W.status_name
FROM Asset as A Inner Join Type as T on A.type_id = T.id
Inner Join Batch as B on A.batch_id = B.id
Left Join Location L on A.location_id = L.id
Left Join Asset A2 on A.parent_id = A2.id
Left Join AssetOwner AO on A.owner_id = AO.owner_id
Left Join Milestone M on A.milestone_id = M.milestone_id
Left Join Workflow as W on W.asset_id = A.id
where A.deleted <= @showDeleted
UNION
Select -1*AR.id as id, AR.asset_id as asset_id, A.name, AR.parent_id as parent_id, A.subPath, T.name as typeName, A2.name as parent_name, B.name as batchName,
L.name as locationName,AO.owner_name as ownerName, T.id as typeID,
M.name as milestoneName, A.deleted as bDeleted, 1 as reference, NULL as phase_name, NULL as status_name
FROM Asset as A Inner Join Type as T on A.type_id = T.id
Inner Join Batch as B on A.batch_id = B.id
Left Join Location L on A.location_id = L.id
Left Join Asset A2 on AR.parent_id = A2.id
Left Join AssetOwner AO on A.owner_id = AO.owner_id
Left Join Milestone M on A.milestone_id = M.milestone_id
Inner Join AssetReference AR on AR.asset_id = A.id
where A.deleted <= @showDeleted
У меня есть хранимая процедура, которая принимает временную таблицу (#temp) и находит все элементы иерархии. Стратегия, которую я использовал, заключалась в следующем:
- Выберите всю системную иерархию во временную таблицу (#treeIDs), представленную списком через запятую каждой ветви дерева.
- Получить полную иерархию запросов на сопоставление ресурсов (из #temp)
- Получить все ссылочные активы, на которые указывают активы, от heirarchy
- Разобрать иерархию всех ссылочных активов
Пока это работает, потому что ссылочные активы всегда являются последним элементом ветви, но если бы их не было, думаю, у меня были бы проблемы. Я чувствую, что мне нужна лучшая форма рекурсии.
Вот мой текущий код, который работает, но я не горжусь им, и я знаю, что он не надежен (потому что он работает только, если ссылки внизу):
Шаг 1. Построить всю иерархию
;WITH Recursive_CTE AS (
SELECT Cast(id as varchar(100)) as Hierarchy, parent_id, id
FROM #assetIDs
Where parent_id is Null
UNION ALL
SELECT
CAST(parent.Hierarchy + ',' + CAST(t.id as varchar(100)) as varchar(100)) as Hierarchy, t.parent_id, t.id
FROM Recursive_CTE parent
INNER JOIN #assetIDs t ON t.parent_id = parent.id
)
Select Distinct h.id, Hierarchy as idList into #treeIDs
FROM ( Select Hierarchy, id FROM Recursive_CTE ) parent
CROSS APPLY dbo.SplitIDs(Hierarchy) as h
Шаг 2. Выберите ветви всех активов, которые соответствуют запросу
Select DISTINCT L.id into #RelativeIDs FROM #treeIDs
CROSS APPLY dbo.SplitIDs(idList) as L
WHERE #treeIDs.id in (Select id FROM #temp)
Шаг 3. Получить все эталонные активы в филиалах (эталонные активы имеют отрицательные значения id, следовательно, id < 0 part)
Select asset_id INTO #REFLinks FROM #AllAssets WHERE id in
(Select #AllAssets.asset_id FROM #AllAssets Inner Join #RelativeIDs
on #AllAssets.id = #RelativeIDs.id Where #RelativeIDs.id < 0)
Шаг 4. Получите ветви всего, что найдено в шаге 3
Select DISTINCT L.id into #extraRelativeIDs FROM #treeIDs
CROSS APPLY dbo.SplitIDs(idList) as L
WHERE
exists (Select #REFLinks.asset_id FROM #REFLinks WHERE #REFLinks.asset_id = #treeIDs.id)
and Not Exists (select id FROM #RelativeIDs Where id = #treeIDs.id)
Я пытался просто показать соответствующий код. Я очень благодарен всем, кто может помочь мне найти лучшее решение!
2 ответа
--getting all of the children of a root node ( could be > 1 ) and it would require revising the query a bit
DECLARE @AssetID int = (select AssetId from Asset where AssetID is null);
--algorithm is relational recursion
--gets the top level in hierarchy we want. The hierarchy column
--will show the row's place in the hierarchy from this query only
--not in the overall reality of the row's place in the table
WITH Hierarchy(Asset_ID, AssetID, Levelcode, Asset_hierarchy)
AS
(
SELECT AssetID, Asset_ID,
1 as levelcode, CAST(Assetid as varchar(max)) as Asset_hierarchy
FROM Asset
WHERE AssetID=@AssetID
UNION ALL
--joins back to the CTE to recursively retrieve the rows
--note that treelevel is incremented on each iteration
SELECT A.Parent_ID, B.AssetID,
Levelcode + 1 as LevelCode,
A.assetID + '\' + cast(A.Asset_id as varchar(20)) as Asset_Hierarchy
FROM Asset AS a
INNER JOIN dbo.Batch AS Hierarchy
--use to get children, since the parentId of the child will be set the value
--of the current row
on a.assetId= b.assetID
--use to get parents, since the parent of the Asset_Hierarchy row will be the asset,
--not the parent.
on Asset.AssetId= Asset_Hierarchy.parentID
SELECT a.Assetid,a.name,
Asset_Hierarchy.LevelCode, Asset_Hierarchy.hierarchy
FROM Asset AS a
INNER JOIN Asset_Hierarchy
ON A.AssetID= Asset_Hierarchy.AssetID
ORDER BY Hierarchy ;
--return results from the CTE, joining to the Asset data to get the asset name
---that is the structure you will want. I would need a little more clarification of your table structure
Это поможет узнать вашу базовую структуру таблицы. Существует два подхода, которые должны работать в зависимости от вашей среды: SQL понимает XML, чтобы вы могли иметь свой SQL в виде структуры xml или просто иметь одну таблицу, в которой каждый элемент строки имел бы уникальный идентификатор первичного ключа и парентид. id это ФК для парентиды. Данные для узла - это просто стандартные столбцы. Вы можете использовать cte или функцию для расчета вычисляемого столбца, чтобы определить степень вложенности для каждого узла. Пределом является то, что узел может иметь только одного родителя.