query - mysql select recursive get all parent with multiple level

Obtener datos de jerarquía de tablas de autorreferencia

Question

Digamos que tienes la siguiente tabla:

items(item_id, item_parent)  

... y es una tabla de item_parent - item_parent refiere a item_id .

¿Qué consulta SQL usaría para SELECCIONAR todos los elementos en la tabla junto con su profundidad, donde la profundidad de un elemento es la suma de todos los padres y abuelos de ese artículo.

Si el siguiente es el contenido de la tabla:

item_id     item_parent
----------- -----------
1           0          
2           0            
3           2          
4           2          
5           3          

... la consulta debería recuperar el siguiente conjunto de objetos:

{"item_id": 1, "profundidad": 0}
{"item_id": 2, "profundidad": 0}
{"item_id": 3, "profundidad": 1}
{"item_id": 4, "profundidad": 1}
{"item_id": 5, "profundidad": 2}

PD: estoy buscando un enfoque compatible con MySQL.

Answer 1

Hay un buen artículo de tecnología en el sitio web mysql sobre datos jerárquicos en MySQL: Gestión de datos jerárquicos en MySQL . Aquí puede encontrar algunas soluciones detalladas con ventajas y desventajas.

Especialmente la parte sobre "El modelo de conjunto anidado" y "Encontrar la profundidad de los nodos" debería ser de su interés.

Answer 2

MySQL

• http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html
• http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

EDITAR: eliminó información innecesaria

Answer 3

Necesito encontrar una solución para la misma tarea, encontré algunos artículos, pero todavía no elegí qué camino tomar ...

http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/

Puede ser que estos enlaces puedan ayudarte. Si encuentra una buena solución, por favor publíquela aquí. No puedo publicar más de un enlace. Agregaré algunos a los próximos mensajes.

Answer 4

Oracle tiene una sintaxis muy conveniente para recuperar datos jerárquicos como este:

select
    item_id,
    item_parent,
    level as depth
from
    items
connect by
    prior item_id = item_parent
start with
    item_parent not in (select item_id from items)

Esto comienza con los nodos raíz de sus árboles como aquellos elementos cuyo item_parent no existe en la tabla como item_id, y selecciona todos los elementos secundarios de esos nodos, junto con su profundidad en el árbol.

Answer 5

Si la base de datos es SQL 2005/2008, entonces ...

La forma más fácil de obtener esto es usar un CTE (Common Table Expression) diseñado para recurse.

 WITH myCTE (Item_id, Depth)
 AS
 (
    Select Item_ID, 0 as Depth From yourTable where Item_Parent=0
    Union ALL
    Select yourTable.Item_ID, Depth + 1 
    From yourTable 
    inner join myCte on yourTable.item_Parent = myCte.Item_Id
 )

 Select Item_id, Depth from myCTE

El resultado es el siguiente:

Item_Id  Depth
    1   0
    2   0
    3   1
    4   1
    5   2

De eso puedes formatearlo como lo desees.