CTE获得父母的所有子女（后代） [英] CTE to get all children (descendants) of a parent

问题描述

我遇到了一个让我头疼的问题...

I've this problem that's giving me a headache...

说，我有一个包含数千行的表以及该表的结构

Say, i've got a table with some thousands of rows and the structure of the table consists on a parent -> child relationship.

这种关系最多可以达到6级。下面是表结构的示例：

The relationships can go up to 6 levels. Here's a sample of the table structure:

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来获取关系，但失败了。

We got a process that loops through the entire table to get the relationships and saves the "levels" column, this process is really slow ( because of the loops ) and i've tried with some cte to get the relationships but failed miserabily.

所以到目前为止，我已经尝试过此CTE，但它并没有达到我的期望，而且，它似乎正在复制行...

So far i've tried this cte but it doesn't do what i was hoping for and also, it seems to be replicating rows...

;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允许我这样做，但是我很讨厌，而且应该在表上计算和更新level列，这可能吗？

As i mentioned early, we got a process that loops the table, it does its job but it can take up to 30 minutes, my question is is there a better way to do this? i know that CTE allows me to do it but i suck at it, also, the levels column should be calculated and updated on the table, is it possible?

Sqlfiddle 万一有人可以提供帮助，谢谢！

Here's a Sqlfiddle in case someone could help, thanks!

推荐答案

应该这样做：

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小提琴。

此外，请查看此链接可为CTE提供帮助...他们一定很高兴知道：

Also, check out this link for help with CTEs... They're definitely good to know:

希望这样做能成功！

这篇关于CTE获得父母的所有子女（后代）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！