如何在循环引用的表中，将所有父级的父级作为子对象的列获取？

Question

我有一个表格，其中的列如下：

entityID, entityName, parentID

我该如何编写查询以返回实体的所有父级别，以便返回类似以下内容：

childentityname, parentlevel1name, parentlevel2name, parentLevel3name and so on

我并不是SQL高手。这个可能吗？如果可以，怎么做呢？

我正在使用Microsoft SQL Server数据库。

- spaghetticowboy

---

我也有这个问题 :p 但请尽量不要将答案限制在这个数据库上。我们使用的是Postgres。我有一个解决方法可以获取 X 级别的结果。但它只能扩展到特定次数，无法无限扩展。如果您想看到这个解决方案，请告诉我，但它并不美观。 - nzifnab

我很想看到它...他们希望这个应用程序是动态的，可以添加层次级别，但是报告让我头疼...幸运的是，表格也有一个级别ID，所以我可以使用一堆case语句来使报告看起来应该如何，但是一旦这些级别改变，我的报告就会出问题...只是幸运的几个。 - spaghetticowboy

好奇一下，这个应用是用什么语言编写的？等我查一下查询。 - nzifnab

等一下...我的回答会给你一个父母表。你想做的是将父母基本上旋转到列中吗？那就完全不同了。 - NullRef

Answer 1

您需要使用递归CTE 请看这里（编辑：仅适用于SQL SERVER 2005+）

大致如下：

WITH recurse_cte (entityID,entityName, parentID, Level)
AS
(
-- Anchor member definition
    SELECT e.entityID,e.entityName, e.parentID,
        0 AS Level
    FROM self_joined AS e
        UNION ALL
-- Recursive member definition
    SELECT e.entityID,e.entityName, e.parentID,
        Level + 1
     FROM self_joined AS e
    INNER JOIN recurse_cte AS cte
        ON e.entityID = cte.parentID
)

select * from recurse_cte

- NullRef

---

这段小代码在我的项目中救了我一命。非常感谢NullRef！太棒了！ - Skatterbrainz

Answer 2

SELECT 
  'accounts'.'id' AS id_0,
  'accounts'.'child_id' AS child_id_0, 
  'child_accounts_1'.'id' AS id_1, 
  'child_accounts_1'.'child_id' AS child_id_1, 
  'child_accounts_2'.'id' AS id_2, 
  'child_accounts_2'.'child_id' AS child_id_2, 
  'child_accounts_3'.'id' AS id_3, 
  'child_accounts_3'.'child_id' AS child_id_3, 
  'child_accounts_4'.'id' AS id_4, 
  'child_accounts_4'.'child_id' AS child_id_4
FROM 
  'accounts' 
LEFT OUTER JOIN 'accounts' 'child_accounts_1'
  ON 'child_accounts_1'.'id' = 'accounts'.'child_id'
LEFT OUTER JOIN 'accounts' 'child_accounts_2'
  ON 'child_accounts_2'.'id' = 'child_accounts_1'.'child_id'
LEFT OUTER JOIN 'accounts' 'child_accounts_3'
  ON 'child_accounts_3'.'id' = 'child_accounts_2'.'child_id'
LEFT OUTER JOIN 'accounts' 'child_accounts_4'
  ON 'child_accounts_4'.'id' = 'child_accounts_3'.'child_id'
WHERE 'accounts'.'id' = 56

这与你正在做的非常相似，只不过我的是一个子级层次结构。

accounts表具有一个属性negative_overflow_account_id，它引用了自身。这里将获取嵌套的前5层的'id'和'negative_overflow_id'。

我在代码中编写了一个循环，根据常量MAX_OVERFLOW生成此查询，当设置为“5”时会生成此查询，并且如果使用不同的数字，则会执行更多/更少操作。

基本上，我的用例是确保某人没有设置无限循环，因此如果达到第5级，则会向用户发送错误消息，告诉他们不能设置得那么深。如果任何一级引用顶级或先前的某个级别，则还会生成错误，指示循环递归（如果允许持续存在，将在后面崩溃应用程序）。

编辑：我缩短了名称。没有人想看到我愚蠢荒谬的命名约定；）

- nzifnab

---

随着递归的出现，这真的不是最好的方法。你能想象这在20层深度时会变成什么样子吗？NullRef的例子在Postgres中也可以工作。 - Andrew Lazarus

哦，我知道随着层级的加深，这种方法会变得非常糟糕。我想看一个使用递归的例子，虽然我不确定我是否完全理解了那些文档（我猜我得更仔细地看一下它们）。看起来是重构我的代码的好地方:p - nzifnab

Answer 3

在Postgres上，这正是WITH RECURSIVE的用途。您可能只需要从（此处链接）文档中更改列名称。

我不知道OP的数据库是否支持递归，可能取决于版本号。如果可用，则语法将类似或相同。如果没有，则会很麻烦。要想做出纯SQL解决方案尤其困难，特别是如果级别数量是无限的。

- Andrew Lazarus