如何在mysql中查询图形/分层数据 [英] How to query graph/hierarchical data in mysql
问题描述
假设我有一个在层次结构中构造的对象表:
Suppose I have a table of objects structured in a hierarchy:
A
|--B
|--C
| +--D
+--E
它们存储在父子表格因此:
They are stored in a "parent-child" table thus:
parent child
A B
A C
C D
A E
如何查询此结构以获得上述定义的结构?我想我需要一些产生这样的信息:
How do I query this to get the structure defined above? I think I need something that produces info like this:
object full_path
A NULL
B A
C A
D A.C
E A
我无法弄清楚如何做对象嵌套多个级别的深度。感觉像我可能需要遍历表(不知道这是否可能在SQL中),否则使用某种我从来没有遇到过的查询。
I cannot figure out how to do the objects nested more than one level deep. It feels like I might need to iterate over the table (no idea if this is possible in SQL), or otherwise use some kind of query I've never encountered before.
其他信息:
- 孩子可能有多个父母现在我很高兴与任何情况下更容易解决的答案。我可以使用简单的
GROUP BY
和MIN
语句将多父结构转换为单个父结构。 li>
- A need not be the only orphan object.
- Children may have multiple parents BUT for now I'm happy with an answer to whichever scenario is easier to solve. I can abide converting a multi-parent structure to a single parent with a simple
GROUP BY
andMIN
statement.
推荐答案
其他答案是正确的,mysql中没有递归功能。此链接介绍如何在mysql中处理层次结构: http:// explainextended。 com / 2009/03/17 / hierarchical-queries-in-mysql /
The other answers are correct, there is no recursive functionality in mysql. This link describes how to approach hierarchies in mysql: http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/
我发现了另外两个SO问题的链接:如何做MySQL循环连接测试如果结果完成? 和 MySql中的分层数据。
I found the link on two other SO questions: How to do MySQL Looped Join which tests if results are complete? and Hierarchical Data in MySql .
这篇关于如何在mysql中查询图形/分层数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!