假设您有一个存储有序树层次结构的平面表:
Id Name ParentId Order
1 'Node 1' 0 10
2 'Node 1.1' 1 10
3 'Node 2' 0 20
4 'Node 1.1.1' 2 10
5 'Node 2.1' 3 10
6 'Node 1.2' 1 20
这是一张图,我们有 [id] Name
. 根节点0是虚构的 .
[0] ROOT
/ \
[1] Node 1 [3] Node 2
/ \ \
[2] Node 1.1 [6] Node 1.2 [5] Node 2.1
/
[4] Node 1.1.1
您将使用什么简约方法将其输出为HTML(或文本,就此而言)作为正确排序,正确缩进的树?
进一步假设你只有基本的数据结构(数组和散列图),没有带有父/子引用的花哨对象,没有ORM,没有框架,只有你的双手 . 该表表示为结果集,可以随机访问 .
伪代码或普通英语是可以的,这纯粹是一个概念性的问题 .
额外的问题:在RDBMS中存储这样的树结构是否有根本更好的方法?
EDITS AND ADDITIONS
回答一个评论者(Mark Bessey)的问题:根节点不是必需的,因为它永远不会被显示 . ParentId = 0是表达"these are top level"的约定 . Order列定义了如何对具有相同父节点的节点进行排序 .
我所谈到的“结果集”可以被描绘成一组哈希图(保留在该术语中) . 因为我的例子意味着已经存在 . 有些答案会加倍努力并首先构建它,但那没关系 .
树可以任意深 . 每个节点可以有N个子节点 . 不过,我并没有考虑到“数百万条目” .
不要错误地选择节点命名('Node 1.1.1')来依赖 . 节点同样可以称为“Frank”或“Bob”,不暗示命名结构,这只是为了使其可读 .
我已经发布了自己的解决方案,所以你们可以把它拉成碎片 .
14 回答
现在,MySQL 8.0即将发布,标准语法为all popular SQL databases will support recursive queries .
我在2017年的演示文稿Recursive Query Throwdown中测试了MySQL 8.0中的递归查询 .
以下是我2008年的原始答案:
有几种方法可以在关系数据库中存储树形结构数据 . 您在示例中显示的内容使用两种方法:
Adjacency List ("parent"专栏)和
Path Enumeration (名称列中的虚线数字) .
另一个解决方案叫做 Nested Sets ,它也可以存储在同一个表中 . 阅读Joe Celko的“Trees and Hierarchies in SQL for Smarties”,了解有关这些设计的更多信息 .
我通常更喜欢名为 Closure Table (又名"Adjacency Relation")的设计来存储树形结构数据 . 它需要另一个表,但是查询树很容易 .
我在我的演讲Models for Hierarchical Data with SQL and PHP和我的书SQL Antipatterns: Avoiding the Pitfalls of Database Programming中介绍了Closure Table .
将所有路径存储在Closure Table中,其中存在从一个节点到另一个节点的直接祖先 . 为每个节点包含一行以引用自身 . 例如,使用您在问题中显示的数据集:
现在你可以从节点1开始得到一棵树,如下所示:
输出(在MySQL客户端中)如下所示:
换句话说,节点3和5被排除,因为它们是单独层次结构的一部分,而不是从节点1下降 .
回复:电子满意的关于直接孩子(或直系亲属)的评论 . 您可以向
ClosureTable
添加“path_length
”列,以便更容易专门查询直接子项或父项(或任何其他距离) .然后,您可以在搜索中添加一个术语,用于查询给定节点的直接子节点 . 这些是
path_length
为1的后代 .来自@ashraf的评论:“如何按名称排序整棵树?”
这是一个示例查询,用于返回作为节点1后代的所有节点,将它们连接到包含其他节点属性(如
name
)的FlatTable,并按名称排序 .来自@Nate的评论:
用户今天建议编辑 . SO主持人批准了编辑,但我正在撤消它 .
编辑建议上面最后一个查询中的ORDER BY应该是
ORDER BY b.path_length, f.name
,大概是为了确保排序与层次结构匹配 . 但是这不起作用,因为它会在"Node 1.2"之后命令"Node 1.1.1" .如果您希望排序以合理的方式匹配层次结构,那么这是可能的,但不是简单地按路径长度排序 . 例如,请参阅我对MySQL Closure Table hierarchical database - How to pull information out in the correct order的回答 .
如果使用嵌套集(有时称为修改的预订树遍历),您可以使用单个查询以树顺序提取整个树结构或其中的任何子树,但代价是插入更昂贵,因为您需要管理通过树结构描述有序路径的列 .
对于django-mptt,我使用了这样的结构:
其中描述了一个看起来像这样的树(
id
表示每个项目):或者,作为嵌套集合图,使
lft
和rght
值如何工作更加明显:如您所见,要以树的顺序获取给定节点的整个子树,您只需选择所有具有
lft
和rght
的行lft
和rght
值之间的值 . 检索给定节点的祖先树也很简单 .为方便起见,
level
列有点非常规化,而tree_id
列允许您为每个顶级节点重新启动lft
和rght
编号,这样可以减少受插入,移动和删除影响的列数,如lft
当进行这些操作以创建或缩小间隙时,必须相应地调整和rght
列 . 当我试图围绕每个操作所需的查询时,我做了一些development notes .在实际处理这些数据以显示树的方面,我创建了一个tree_item_iterator实用程序函数,对于每个节点,它应该为您提供足够的信息来生成您想要的任何类型的显示 .
有关更多信息MPTT:
Trees in SQL
Storing Hierarchical Data in a Database
Managing Hierarchical Data in MySQL
从Oracle 9i开始,您可以使用CONNECT BY .
从SQL Server 2005开始,您可以使用递归公用表表达式(CTE) .
两者都将输出以下结果 .
这是一个相当古老的问题,但由于它有很多观点,我认为值得提出一个替代方案,并且在我看来非常优雅的解决方案 .
要读取树结构,可以使用 recursive Common Table Expressions (CTE) . 它提供了一次获取整个树结构的可能性,具有关于节点级别,其父节点和父节点的子节点内的顺序的信息 .
让我告诉你这在PostgreSQL 9.1中是如何工作的 .
结果如下:
树节点按深度级排序 . 在最终输出中,我们将在后续行中显示它们 .
对于每个级别,它们按父级中的parent_id和node_order排序 . 这告诉我们如何在输出中呈现它们 - 按此顺序将链接节点呈现给父节点 .
拥有这样的结构,在HTML中制作一个非常好的演示文稿并不困难 .
递归CTE可在 PostgreSQL, IBM DB2, MS SQL Server and Oracle 中找到 .
如果您想阅读有关递归SQL查询的更多信息,可以查看您喜欢的DBMS的文档,或阅读我的两篇文章,内容涉及此主题:
Do It In SQL: Recursive Tree Traversal
Get to know the power of SQL recursive queries
比尔的答案非常好,这个答案增加了一些东西,这使我希望SO支持线程答案 .
无论如何,我想支持树结构和Order属性 . 我在每个节点中包含一个名为
leftSibling
的属性,它在原始问题中执行相同的操作Order
(维持从左到右的顺序) .More detail and SQL code on my blog .
谢谢比尔,你的回答有助于入门!
在给出选择的情况下,我为每个记录创建一个对象,其中每个对象都有一个
children
集合,并将它们全部存储在一个关联数组(/ hashtable)中,其中Id是关键字 . 并通过该系列闪电战,将孩子们加入相关的儿童田地 . Simple.但是因为你通过限制使用一些好的OOP而没有乐趣,我可能会根据以下内容进行迭代:
编辑:这类似于其他几个条目,但我认为它是's slightly cleaner. One thing I' ll add:这是非常SQL密集型的 . 这很讨厌 . If you have the choice, go the OOP route.
这是写得很快,既不漂亮也不高效(再加上autoboxes很多,在
int
和_692590之间进行转换很烦人!),但它确实有效 .它可能违反了规则,因为我正在创建自己的对象,但是嘿,我这样做是为了转移实际工作:)
这也假设在开始构建节点之前,resultSet / table被完全读入某种结构,如果你有数十万行,这将不是最好的解决方案 .
假设您知道根元素为零,这是输出到文本的伪代码:
您可以使用散列映射模拟任何其他数据结构,因此这不是一个可怕的限制 . 从顶部扫描到底部,您可以为数据库的每一行创建一个哈希映射,每个列都有一个条目 . 将每个哈希映射添加到“主”哈希映射,键入id . 如果任何节点具有您尚未看到的“父”,请在主哈希映射中为其创建占位符条目,并在看到实际节点时将其填入 .
要将其打印出来,请执行简单的深度优先传递数据,并沿途跟踪缩进级别 . 您可以通过为每行保留“子”条目并在扫描数据时填充它来使这更容易 .
至于是否有一种“更好”的方式将树存储在数据库中,这取决于您将如何使用数据 . 我已经看到了具有已知最大深度的系统,它为层次结构中的每个级别使用了不同的表 . 如果树中的级别毕竟不是完全相同的(顶级类别不同于),那么这很有意义树叶) .
有很好的解决方案可以利用sql索引的内部btree表示 . 这是基于1998年左右的一些重大研究 .
这是一个示例表(在mysql中) .
树表示所需的唯一字段是:
tw:从左到右DFS预订单索引,其中root = 1 .
pa:对父节点的引用(使用tw),root为null .
sz:节点分支的大小包括自身 .
nc:用作语法糖 . 它是tw nc并代表节点的_t的两个"next child" .
这是一个示例24节点填充,按tw排序:
每个树结果都可以非递归地完成 . 例如,要在tw = '22'获取节点的祖先列表
Ancestors
兄弟姐妹和孩子是微不足道的 - 只需使用tw字段排序 .
Descendants
例如,以tw = 17为根的节点的集合(分支) .
Additional Notes
当存在比插入或更新更多的读取时,此方法非常有用 .
因为树中节点的插入,移动或更新需要调整树,所以必须在开始操作之前锁定表 .
插入/删除成本很高,因为需要在插入点之后的所有节点上更新tw索引和sz(分支大小)值,并且分别针对所有祖先更新 .
分支移动涉及将分支的tw值移出范围,因此在移动分支时还必须禁用外键约束 . 移动分支需要四个查询:
将分支移出范围 .
缩小它留下的空隙 . (剩下的树现在已标准化) .
打开它会去的差距 .
将分支移动到新位置 .
Adjust Tree Queries
树中间隙的打开/关闭是创建/更新/删除方法使用的重要子功能,因此我将其包含在此处 .
我们需要两个参数 - 一个表示我们是否正在缩小规模或升级的标志,以及节点的tw索引 . 因此,例如tw = 18(其分支大小为5) . 让我们假设我们正在缩小规模(删除tw) - 这意味着我们在以下示例的更新中使用' - '而不是'' .
我们首先使用(略微改变的)祖先函数来更新sz值 .
然后我们需要为tw高于要移除的分支的那些调整tw .
然后我们需要为pa的tw高于要删除的分支的那些调整父级 .
如果可以创建嵌套的哈希映射或数组,那么我可以从头开始向下移动表并将每个项添加到嵌套数组中 . 我必须跟踪每个行到根节点,以便知道要插入的嵌套数组中的哪个级别 . 我可以使用memoization,这样我就不需要一遍又一遍地查找同一个父母 .
编辑:我会先将整个表读入一个数组,因此不会重复查询数据库 . 当然,如果您的 table 非常大,这将不切实际 .
在构建结构之后,我必须首先遍历它并打印出HTML .
没有更好的基本方法来使用一个表来存储这些信息(虽然我可能是错的;),并且希望看到更好的解决方案) . 但是,如果您创建一个使用动态创建的数据库表的方案,那么您在牺牲简单性和SQL地狱风险的情况下开辟了一个全新的世界;) .
如果元素按树顺序排列,如示例所示,则可以使用类似以下Python示例的内容:
这样做是维护一个表示树中当前位置的堆栈 . 对于表中的每个元素,它会弹出堆栈元素(关闭匹配的div),直到找到当前项的父元素 . 然后它输出该节点的开始并将其推送到堆栈 .
如果要使用缩进而不是嵌套元素输出树,可以简单地跳过print语句来打印div,并在每个项之前打印一些空格,这些空格等于堆栈大小的某个倍数 . 例如,在Python中:
您还可以轻松地使用此方法构造一组嵌套列表或词典 .
编辑:我从您的澄清中看到,这些名称并非旨在成为节点路径 . 这表明了另一种方法:
这构造了一个元组数组树(!) . idx [0]表示树的根 . 数组中的每个元素都是一个2元组,由节点本身和所有子节点组成 . 一旦构造,您可以保持idx [0]并丢弃idx,除非您想通过其ID访问节点 .
要扩展Bill的SQL解决方案,您基本上可以使用平面阵列执行相同的操作 . 更进一步,如果您的字符串都具有相同的长度并且您的最大子项数已知(例如在二叉树中),您可以使用单个字符串(字符)来完成数组) . 如果你有任意数量的孩子,这会使事情变得复杂......我将不得不检查我的旧笔记,看看能做些什么 .
然后,牺牲一点内存,特别是如果你的树稀疏和/或不平静,你可以通过一些索引数学,通过存储你的树,宽度优先在数组中随机访问所有字符串(对于二进制文件)树):
你知道你的字符串长度吗?
我现在在工作,所以不能花太多时间在它上面,但有兴趣的话,我可以获取一些代码来做到这一点 .
我们用它来搜索由DNA密码子构成的二叉树,一个构建树的过程,然后我们将它展平以搜索文本模式,当找到时,虽然索引数学(从上面反转)我们得到节点...非常快速高效,坚韧我们的树很少有空节点,但我们可以快速搜索数十亿字节的数据 .
考虑使用像neo4j这样的nosql工具来实现层次结构 . 例如,像Linkedin这样的网络应用程序使用couchbase(另一个nosql解决方案)
但是,仅将nosql用于数据集市级别查询,而不是存储/维护事务