recursivas - consulta jerarquica mysql

Contando el número de hijos en datos SQL jerárquicos

Question

para una estructura de datos simple tal como:

ID    parentID    Text        Price
1                 Root
2     1           Flowers
3     1           Electro
4     2           Rose        10
5     2           Violet      5
6     4           Red Rose    12
7     3           Television  100
8     3           Radio       70
9     8           Webradio    90

Para referencia, el árbol de jerarquía se ve así:

ID    Text        Price
1     Root
|2    Flowers
|-4   Rose        10
| |-6 Red Rose    12
|-5   Violet      5
|3    Electro
|-7   Television  100
|-8   Radio       70
  |-9 Webradio    90

Me gustaría contar el número de niños por nivel. Así que obtendría una nueva columna "NoOfChildren" así:

ID    parentID    Text        Price  NoOfChildren
1                 Root               8
2     1           Flowers            3
3     1           Electro            3
4     2           Rose        10     1
5     2           Violet      5      0
6     4           Red Rose    12     0
7     3           Television  100    0
8     3           Radio       70     1
9     8           Webradio    90     0

Leí algunas cosas acerca de los datos jerárquicos, pero de alguna manera me quedo atascado en las múltiples uniones internas en los ID de padres. Quizás alguien pueda ayudarme aquí.

Answer 1

Considere el uso de una forma transversal de árbol de preorden modificada para almacenar los datos jerárquicos. Consulte http://www.sitepoint.com/hierarchical-data-database/

Determinar el número de hijos para cualquier nodo se convierte en un simple:

SELECT (right-left-1) / 2 AS num_children FROM ...

Answer 2

Usando un CTE obtendrías lo que quieres.

• Recursivamente recorren todos los niños, recordando la raíz.
• COUNT los elementos para cada raíz.
• JOIN estos nuevamente con su tabla original para producir los resultados.

Datos de prueba

DECLARE @Data TABLE (
  ID INTEGER PRIMARY KEY
  , ParentID INTEGER
  , Text VARCHAR(32)
  , Price INTEGER
)

INSERT INTO @Data
  SELECT 1, Null, ''Root'', NULL
  UNION ALL SELECT 2, 1, ''Flowers'', NULL
  UNION ALL SELECT 3, 1, ''Electro'', NULL
  UNION ALL SELECT 4, 2, ''Rose'', 10
  UNION ALL SELECT 5, 2, ''Violet'', 5
  UNION ALL SELECT 6, 4, ''Red Rose'', 12
  UNION ALL SELECT 7, 3, ''Television'', 100
  UNION ALL SELECT 8, 3, ''Radio'', 70
  UNION ALL SELECT 9, 8, ''Webradio'', 90

Declaración SQL

;WITH ChildrenCTE AS (
  SELECT  RootID = ID, ID
  FROM    @Data
  UNION ALL
  SELECT  cte.RootID, d.ID
  FROM    ChildrenCTE cte
          INNER JOIN @Data d ON d.ParentID = cte.ID
)
SELECT  d.ID, d.ParentID, d.Text, d.Price, cnt.Children
FROM    @Data d
        INNER JOIN (
          SELECT  ID = RootID, Children = COUNT(*) - 1
          FROM    ChildrenCTE
          GROUP BY RootID
        ) cnt ON cnt.ID = d.ID