SQL Server中多层次结构的数据结构 [英] Data structure for many to many hierarchies in SQL Server

问题描述

我在系统中已经有以下数据结构。

ItemDetails ：

  ID名称
 -------- 
 1 XXX 
 2 YYY 
 3 ZZZ 
 4 TTT 
 5 UUU 
 6 WWW 
  

而层次结构在单独的表格中（有多对多关系）

ItemHierarchy ：

  ParentCode ChildCode 
 -------------------- 
 1 2 
 1 3 
 3 4 
 4 5 
 5 3 
 5 6 
  

正如你可以看到3是1和3的子节点。我想遍历记录，例如从节点3。

I需要编写一个存储过程，并获得3的所有祖先和3的所有子节点。

可以让我知道是否有可能拉数据？如果是这样，哪个数据结构就可以了。

请注意，我的表格包含100万条记录，其中40％有多个层次结构。

我根据层次结构进行了CTE级别的增量处理，但是当我们从根到叶级节点的遍历时，我得到最大的递归错误。我已经尝试了'HierarchyID'，但是当它有一个节点的多个父节点时无法获取所有的细节。

更新：我可以设置递归限制为max并运行查询。因为它有数百万行，我无法获得输出。

我想创建一个数据结构，使其能够从顶部提供信息底部或底部到顶部（在任何节点级别）。

有人可以帮助我吗？

解决方案

不建议使用RDBMS进行分层数据结构，为什么

I have the following data structure already in the system.

ItemDetails:

ID Name
--------
1  XXX
2  YYY
3  ZZZ
4  TTT
5  UUU
6  WWW

And the hierarchies are in separate table (with many to many relationships)

ItemHierarchy:

ParentCode ChildCode
--------------------
1            2
1            3
3            4
4            5
5            3
5            6

As you can see that 3 is child node for 1 and 3. I want to traverse records say for example that from the node 3.

I need to write a stored procedure and get all the ancestors of 3 and all the child nodes of 3.

Could you please let me know whether any possibilities to pull the data? If so, which data structure is OK for it.

Please note that my table is containing 1 million records and out of it 40% are having multiple hierarchies.

I did 'CTE' with level and incrementing it based upon the hierarchy but I'm getting max recursive error when we traverse from root to leaf level node. I have tried 'HierarchyID' but unable to get all the details when its having multiple parent for a node.

Update: I can set a recursion limit to max and run the query. Since it has millions of rows, I'm unable to get the output at all.

I want to create a data structure such that its capable to giving information from top to bottom or bottom to top (at any node level).

Could someone kindly please help me with that?

解决方案

Using RDBMS for hierarchical data structure is not recommended, its why graph database have been created.

BTW following Closure Table pattern will help you.
The Closure Table solution is a simple and elegant way of storing hierarchies. It involves storing all paths through the tree, not just those with a direct parent-child relationship.

The key point to use the pattern is how you must fill ItemHierarchy table.
Store one row in this table for each pair of nodes in the tree that shares an ancestor/descendant relationship, even if they are separated by multiple levels in the tree. Also add a row for each node to reference itself.
Think we have a simple graph like bellow:
The doted arrows shows the rows in ItemHierarchy table:
To retrieve descendants of #3:

SELECT c.*
FROM ItemDetails AS ID
JOIN ItemHierarchy AS IH ON ID.ID = IH.ChildCode
WHERE IH.ParentCode = 3;

To retrieve ancestors of #3:

SELECT c.*
FROM ItemDetails AS ID
JOIN ItemHierarchy AS IH ON ID.ID = IH.ParentCode 
WHERE IH.ChildCode = 3;

To insert a new leaf node, for instance a new child of #5, first insert the self-referencing row. Then add a copy of the set of rows in TreePaths that reference comment #5 as a descendant (including the row in which #5 references itself), replacing the descendant with the number of the new item: INSERT INTO ItemHierarchy (parentCode, childCode)

SELECT IH.parentCode, 8
FROM ItemHierarchy AS IH
WHERE IH.childCode = 5
UNION ALL
SELECT 8, 8;

To delete a complete sub-tree, for instance #4 and its descendants, delete all rows in ItemHierarchy that reference #4 as a descendant, as well as all rows that reference any of #4’s descendants as descendants:

DELETE FROM ItemHierarchy 
WHERE chidCode IN (SELECT childCode
FROM ItemHierarchy 
WHERE parrentCode = 4);

UPDATE
Since the sample data you have shown us leads to recursive loops(not hierarchies) like:

1 -> 3 -> 4 -> 5 -> 3 -> 4 -> 5

Following Path Enumeration pattern will help you.
A UNIX path like /usr/local/lib/ is a path enumeration of the file system, where usr is the parent of local, which in turn is the parent of lib.
You can create a Table or View from ItemHierarchy table, calling it EnumPath:
Table EnumPath(NodeCode, Path)

For the sample data we will have:

To find ancestors of node #4:

select distinct E1.NodeCode from EnumPath E1
inner join EnumPath E2
On E2.path like E1.path || '%'
where E2.NodeCode = 4 and E1.NodeCode != 4;

To find descendants of node #4:

select distinct E1.NodeCode from EnumPath E1
inner join EnumPath E2
On E1.path like E2.path || '%'
where E2.NodeCode = 4 and E1.NodeCode != 4;

Sqlfiddle demo

这篇关于SQL Server中多层次结构的数据结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！