获取父节点的所有子孙节点（后代）的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

我们有一个进程，通过整个表循环以获取关系并保存“levels”列，但此过程非常缓慢（因为循环），我尝试使用一些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，如果有人能帮忙，谢谢！

- Sam Ccp

---

你知道CTE可以是递归的吗？请看这里：http://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/ - Mike Cheel

提示：一种常见的设计是使ParentId可为空，并使用NULL表示没有父行。这避免了使用“魔数”来表示顶级行或使顶级行成为其自己的父级的混淆，即ProductId 1174与ParentId 1174。 - HABO

@MikeCheel 在我的 CTE 示例中，我正在进行递归调用。是吗？ - Sam Ccp

@HABO 我知道这不是最美观的设计，但这是一个很少使用的遗留系统，我得到了维护它的胜利者门票，并且正在尝试将所有的数据库内容更新为存储过程。我所指的这个过程是一个asp文件，里面有大量的select、update、delete和insert语句。感谢你的帮助:D - Sam Ccp

@Sam 是的，你说得对。可能是因为我今天很累了，所以读错了。 - Mike Cheel

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的帮助... 它们确实很有用：

希望这可以解决问题！

- John Bustos

---

太好了！这确实解决了问题，但我有一个问题，为什么我的尝试失败得那么彻底，除了级别列的格式之外，我的代码与你的代码并没有太大区别呢？ - Sam Ccp

你将 Levels 插入了你的 CTE 中，然后只是简单地查询它... 这破坏了你试图实现的想法。如果你看一下我的 SQLFiddle，原始表甚至没有 Levels 列 - 我们根据产品/父级列创建它，正如所需的那样... - John Bustos

在 WITH 之前需要加上 ;。 - Null Head

@NullHead 不需要，with 只需要是语句中的第一个关键字。如果您正确地使用分号终止了之前的语句，则不需要在 with 前面添加分号。 - iamdave

如果两个产品具有相同的parentid，这会起作用吗？ - Raghurocks

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

- Charles Bretana

---

谢谢您的帮助，但是这种方法没有正确获取层级，它只是一个递增的值。想法是基于父子关系获取层级列。 - Sam Ccp

你是指“基于父子关系”吗？如果不是每个父子连接都递增值？ - Charles Bretana

例如，产品ID 311的级别列是311、1174，这就是它们之间的关系，也许我解释得不太清楚？ - Sam Ccp

或者这些级别只是在向上遍历层次结构时每个级别中项目的产品ID吗？如果是这样，请尝试上面的编辑版本。 - Charles Bretana

在这种情况下，代表产品及其关系，例如，产品311可以有一个父级1174，但也可以有一个2080的父级，或许更好地解释一下，这个表不仅保存了产品目录，还保存了所有食谱的“成分”，例如番茄沙拉有一个ID 3000，它的所有内容都将在levels列中，使您可以简单地查询此列并获取食谱的所有成分。 - Sam Ccp