在SQL树结构中查找节点 [英] Find Nodes in SQL Tree Structure
问题描述
我在MYSQL数据库中存储了一个类似树的数据模型.通过在每个节点"对象上使用两个字段来遍历该树:
I have a tree-like data model stored in a MYSQL database. The tree is traversed by using two fields on each 'node' object:
- id
- parent_id
根ID的parent_id为'null'，树中的所有其他节点都引用了一个父ID.
The root id has a parent_id of 'null' and all other nodes in the tree reference a parent id.
如果要获取此树中所有节点ID的列表，则必须使用实质上是许多查询的for循环，以递归方式遍历该树并收集每个节点的一个或多个属性.
If I want to get a list of all node ids in this tree, I have to recursively traverse the tree and collect one or more attributes of each node, using what is essentially a for loop with many queries.
我想知道是否有一种方法可以在SQL查询中更有效地做到这一点
I was wondering if there was a way to do this all more efficiently with on query in SQL
推荐答案
I wrote a highly-rated post about a solution I call Closure Table: What is the most efficient/elegant way to parse a flat table into a tree?
我还在我的演示文稿中介绍了该设计具有SQL和PHP 和我的书 SQL反模式:避免数据库编程的陷阱
I also cover that design in my presentation Models for Hierarchical Data with SQL and PHP and in my book SQL Antipatterns: Avoiding the Pitfalls of Database Programming.
我已经在Stack Overflow上回答了有关分层数据查询的许多问题: https://stackoverflow.com/search?q=user%3A20860+%5Bhierarchical-data%5D
And I've answered questions about hierarchical-data queries many times on Stack Overflow: https://stackoverflow.com/search?q=user%3A20860+%5Bhierarchical-data%5D
Quassnoi 撰写了一系列有关MySQL查询树的史诗文章: http://explainextended.com/2009/07 /22/hierarchial-queries-in-mysql-identification-trees/
Quassnoi has written an epic series of blog articles about querying trees in MySQL: http://explainextended.com/2009/07/22/hierarchial-queries-in-mysql-identifying-trees/
Quassnoi还回答了关于堆栈数据有关分层数据的许多问题: https://stackoverflow.com/search?q=user%3A55159+%5Bhierarchical-data%5D
Quassnoi has also answered a number of questions on Stack Overflow about hierarchical-data: https://stackoverflow.com/search?q=user%3A55159+%5Bhierarchical-data%5D
这篇关于在SQL树结构中查找节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
