CTE, чтобы получить всех детей (потомков) родителей

Question

У меня эта проблема, которая вызывает у меня головную боль...

Скажем, у меня есть таблица с несколькими тысячами строк, и структура таблицы состоит из родительских -> дочерних отношений.

Отношения могут идти до 6 уровней. Вот пример структуры таблицы:

ProductId | ParentId | Levels
1174           0        1174
311           1174      311, 1174
1186          311       1186, 311, 1174
448           1186      448, 1186, 311, 1174
3365          448       3365, 448, 1186, 311, 1174

У нас есть процесс, который проходит по всей таблице, чтобы получить отношения, и сохраняет столбец "уровни", этот процесс действительно медленный (из-за циклов), и я попытался с помощью некоторого cte получить отношения, но потерпел неудачу.

До сих пор я пробовал этот cte, но он не делает то, на что я надеялся, а также, похоже, он реплицирует строки...

;With Parents(ProductId, ParentId, Levels)
As(
  Select ProductId, ParentId, Levels
  From Products
  Where ParentId = 0 
  Union All
  Select p.ProductId, p.ParentId, p.Levels
  From Products p
  Join Parents cte On cte.ProductId = p.ParentId
)
Select *
From Parents

Как я упоминал ранее, у нас есть процесс, который зацикливает таблицу, он выполняет свою работу, но это может занять до 30 минут, мой вопрос, есть ли лучший способ сделать это? я знаю, что CTE позволяет мне это делать, но я отстой, и столбец уровней должен быть рассчитан и обновлен в таблице, возможно ли это?

Вот Sqlfiddle на случай, если кто-то может помочь, спасибо!

Answer 1

Это должно сделать это:

WITH MyTest as
(
  SELECT P.ProductID, P.ParentID, CAST(P.ProductID AS VarChar(Max)) as Level
  FROM Products P
  WHERE P.ParentID = 0

  UNION ALL

  SELECT P1.ProductID, P1.ParentID, CAST(P1.ProductID AS VarChar(Max)) + ', ' + M.Level
  FROM Products P1  
  INNER JOIN MyTest M
  ON M.ProductID = P1.ParentID
 )
SELECT * From MyTest

А вот и обновленная SQL Fiddle.

Кроме того, проверьте эту ссылку для помощи с CTE... Им определенно полезно знать:

Надеюсь, это сработает!

Answer 2

;With Parents(ProductId, ParentId, Level, levels)
As(
  Select ProductId, ParentId, 0, 
     cast(ltrim(str(productId,8,0)) as varchar(max))
  From Products
  Where ParentId = 0 
  Union All
  Select p.ProductId, p.ParentId, 
      par.Level + 1,
      cast( levels + ', ' + ltrim(str(productId,8,0)) as varchar(max))
  From Products p
     Join Parents par
        On par.ProductId = p.ParentId
  )
  Select * From Parents
  Order By Level