我正在处理一个具有层次结构的资产数据库。此外，还有一个"ReferenceAsset"表，它有效地指向一个资产。参考资产的基本功能是覆盖，但它被选中，就好像它是一个唯一的新资产一样。其中一个被设置的覆盖是parent_id。
与选择层次结构相关的列:
资产:id (primary)， parent_id
资产引用:id (primary)， asset_id (foreignkey->Asset)， parent_id(总是一个资产)
——编辑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
目前我的解决方案适用于此，但它仅限于一个深度的引用(我觉得实现是相当丑陋的)。
——编辑这是我的主要选择函数。这将更好地说明真正的复杂之处:资产引用。
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)
- 从层次 中获取由assets指向的所有参考资源
- 解析所有参考资源的层次结构
这工作现在，因为参考资产总是最后一个项目的分支，但如果他们不是，我想我将陷入困境。我觉得我需要一些更好的递归形式。
这是我目前的代码，这是工作，但我不自豪，我知道它是不健壮的(因为它只工作，如果引用是在底部):
步骤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 <</p> 0部分)
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)
我试着只显示相关的代码。我非常感谢任何人谁可以帮助我找到一个更好的解决方案!
--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和parentid。Id是父项的fk。节点的数据只是标准列。可以使用cte或为计算列赋能的函数来确定每个节点的嵌套程度。限制是一个节点只能有一个父节点。