如何在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.

其他信息：

2. 孩子可能有多个父母现在我很高兴与任何情况下更容易解决的答案。我可以使用简单的 GROUP BY 和 MIN 语句将多父结构转换为单个父结构。 li>
   

1. A need not be the only orphan object.
2. 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 and MIN 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屋！