SQL 查询组织结构图？
我觉得这可能是一个常见问题，但从我的谷歌搜索中，我找不到针对我的问题的解决方案。
我的数据库中有一个组织列表（表），我需要能够根据其层次结构运行查询。 例如，如果您查询最高的组织，我想返回该组织下列出的所有组织的 ID。 此外，如果我查询中等范围的组织，我只想在该组织下列出组织 ID。
a) 设置数据库模式和 b) 查询的最佳方法是什么？ 我只想发送最顶层的组织 ID，然后获取该组织下的 ID。
我认为这是有道理的，但如果有必要我可以澄清。
I feel that this is likely a common problem, but from my google searching I can't find a solution quite as specific to my problem.
I have a list of Organizations (table) in my database and I need to be able to run queries based on their hierarchy. For example, if you query the highest Organization, I would want to return the Id's of all the Organizations listed under that Organization. Further, if I query an organization sort of mid-range, I want only the Organization Id's listed under that Organization.
What is the best way to a) set up the database schema and b) query? I want to only have to send the topmost Organization Id and then get the Id's under that Organization.
I think that makes sense, but I can clarify if necessary.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
正如我在评论中所承诺的，我挖出了一篇文章将层次结构存储在数据库中，允许恒定时间检索任意子树。 我认为它比当前标记为已接受的答案更适合您的需求，无论是在易用性还是访问速度方面。 我可以发誓我最初在维基百科上看到了同样的概念，但现在找不到了。 它显然被称为“修改的先序树遍历”。 它的要点是对树中的每个节点进行两次编号，同时进行深度优先遍历，一次在向下遍历，一次在返回途中（即，当您在递归实现中展开堆栈时） 。 这意味着给定节点的子节点的所有数字都位于该节点的两个数字之间。 在这些列上添加索引，您就可以进行非常快速的查找。 我确信这是一个糟糕的解释，所以请阅读这篇文章，其中包含更深入的内容并包含图片。
As promised in my comment, I dug up an article on how to store hierarchies in a database that allows constant-time retrieval of arbitrary subtrees. I think it will suit your needs much better than the answer currently marked as accepted, both in ease of use and speed of access. I could swear I saw this same concept on wikipedia originally, but I can't find it now. It's apparently called a "modified preorder tree traversal". The gist of it is you number each node in the tree twice, while doing a depth-first traversal, once on the way down, and once on the way back up (i.e. when you're unrolling the stack, in a recursive implementation). This means that the children of a given node have all their numbers in between the two numbers of that node. Throw an index on those columns and you've got really fast lookups. I'm sure that's a terrible explanation, so read the article, which goes into more depth and includes pictures.
一种简单的方法是将组织的来源存储在文本字段中，例如：
SALES-EUROPE-NORTH
要搜索每个销售组织，您可以在 SALES-% 上查询。 对于每个欧洲销售组织，查询 SALES-EUROPE-%。
如果您重命名组织，请注意同时更新其子组织。
这保持了简单，无需递归，但牺牲了一些灵活性。
One simple way is to store the organization's parentage in a text field, like:
SALES-EUROPE-NORTH
To search for every sales organization, you can query on SALES-%. For each European sales org, query on SALES-EUROPE-%.
If you rename an organization, take care to update its child organizations as well.
This keeps it simple, without recursion, at the cost of some flexibility.
最简单的方法是有一个 ParentID 列，它是同一个表中 ID 列的外键，对于根节点为 NULL。 但这种方法有一些缺点。
嵌套集是在关系数据库中存储树的有效方法。
The easy way is to have a ParentID column, which is a foreign key to the ID column in the same table, NULL for root nodes. But this method has some drawbacks.
Nested sets are an efficient way to store trees in an relational database.
您可以让一个组织具有 id PK 和对该 id 的父 FK 引用。 然后对于查询，使用（如果您的数据库后端支持）递归查询，也称为通用表表达式。
You could have an Organization have an id PK and a parent FK reference to the id. Then for the query, use (if your database backend supports them) recursive queries, aka Common Table Expressions.