SQL Server：查询分层和引用的数据

I'm working on an asset database that has a hierarchy. Also, there is a "ReferenceAsset" table, that effectively points back to an asset. The Reference Asset basically functions as an override, but it is selected as if it were a unique, new asset. One of the overrides that gets set, is the parent_id.

我正在研究一个具有层次结构的资产数据库。此外,还有一个“ReferenceAsset”表,可以有效地指回资产。参考资产基本上用作覆盖,但它被选择为好像它是一个独特的新资产。设置的覆盖之一是parent_id。

Columns that are relevant to selecting the heirarchy:
Asset: id (primary), parent_id
Asset Reference: id (primary), asset_id (foreignkey->Asset), parent_id (always an Asset)
---EDITED 5/27----

与选择层次结构相关的列:资产:id(主要),parent_id资产参考:id(主要),asset_id(foreignkey-> Asset),parent_id(始终为资产)--- EDITED 5/27 ----

Sample Relevent Table Data (after joins):

样本Relevent表数据(连接后):

   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

the id < 0 (like the last row) signify assets that are referenced. Referenced assets have a few columns that are overidden (in this case, only parent_id is important).

id <0(如最后一行)表示引用的资产。引用的资产有几个被覆盖的列(在这种情况下,只有parent_id很重要)。

The expectation is that if I select all assets from april, I should do a secondary select to get the entire tree branches of the matching query:

期望是如果我从四月选择所有资产,我应该进行二次选择以获得匹配查询的整个树分支:

so initially the query match would result in:

所以最初查询匹配会导致:

    4       4       suit_banker         3          april      texture

Then after the CTE, we get the complete hierarchy and our result should be this (so far this is working)

然后在CTE之后,我们得到完整的层次结构,我们的结果应该是这个(到目前为止这是有效的)

    3       3       suit               null        march      shape
    4       4       suit_banker         3          april      texture
   -5       6       tie_red             4          march      texture

and you see, the parent of id:-5 is there, but what is missing, that is needed, is the referenced asset, and the parent of the referenced asset:

你看,id:-5的父亲在那里,但缺少的是需要的,是引用的资产,以及引用资产的父级:

    5       5       tie                null        march      shape
    6       6       tie_red             5          march      texture

Currently my solution works for this, but it is limited to only a single depth of references (and I feel the implementation is quite ugly).

目前我的解决方案适用于此,但它仅限于一个参考深度(我觉得实现非常难看)。

---Edited---- Here is my primary Selection Function. This should better demonstrate where the real complication lies: the AssetReference.

---编辑----这是我的主要选择功能。这应该更好地证明真正的复杂性在哪里: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

I have a stored procedure that takes a temp table (#temp) and finds all the elements of the hierarchy. The strategy I employed was this:

我有一个存储过程,它接受临时表(#temp)并查找层次结构的所有元素。我采用的策略是这样的:

1. Select the entire system heirarchy into a temp table (#treeIDs) represented by a comma separated list of each entire tree branch

选择整个系统heirarchy到临时表(#treeIDs)中,由每个整个树分支的逗号分隔列表表示

2. Get entire heirarchy of assets matching query (from #temp)

获取匹配查询的资产的整个层次(来自#temp)

3. Get all reference assets pointed to by Assets from heirarchy

获取来自heirarchy的Assets指向的所有参考资产

4. Parse the heirarchy of all reference assets

解析所有参考资产的层次结构

This works for now because reference assets are always the last item on a branch, but if they weren't, i think i would be in trouble. I feel like i need some better form of recursion.

这是有效的,因为参考资产总是分支上的最后一项,但如果它们不是,我想我会遇到麻烦。我觉得我需要一些更好的递归形式。

Here is my current code, which is working, but i am not proud of it, and I know it is not robust (because it only works if the references are at the bottom):

这是我当前的代码,它正在运行,但我并不为此感到自豪,而且我知道它不健壮(因为它仅在引用位于底部时才有效):

Step 1. build the entire hierarchy

步骤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

Step 2. Select the branches of all assets that match the query

步骤2.选择与查询匹配的所有资产的分支

Select DISTINCT L.id into #RelativeIDs FROM #treeIDs
CROSS APPLY dbo.SplitIDs(idList) as L
WHERE #treeIDs.id in (Select id FROM #temp)

Step 3. Get all Reference Assets in the branches (Reference assets have negative id values, hence the id < 0 part)

步骤3.获取分支中的所有参考资产(参考资产具有负id值,因此id <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)

Step 4. Get the branches of anything found in step 3

步骤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)

I've tried to just show the relevant code. I am super grateful to anyone who can help me find a better solution!

我试图只显示相关代码。我非常感谢能帮助我找到更好解决方案的人!

2 个解决方案

#1

--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  

#2

It would help to know your underlying table structure. There are two approaches which should work depending on your environment: SQL understands XML so you could have your SQL as an xml structure or simply have a single table with each row item having a unique primary key id and a parentid. id is the fk for the parentid. The data for the node are just standard columns. You can use a cte or a function powering a calculated column to determin the degree of nesting for each node. The limit is that a node can only have one parent.

了解您的基础表结构将有所帮助。根据您的环境,有两种方法可以使用:SQL了解XML,因此您可以将SQL作为xml结构,或者只使用一个表,每个行项都具有唯一的主键ID和parentid。 id是parentid的fk。节点的数据只是标准列。您可以使用cte或为计算列供电的函数来确定每个节点的嵌套程度。限制是节点只能有一个父节点。