c# - SQL 从自引用表中获取父级

Question

我有一个这样的表:

Item
{
   int ItemID
   int ParentID 
   string Name
}

Item实际上是一个更大的table Object的子集:

Object
{
    int ObjectID
    string Color
}

所以 ItemID 是 ObjectID 的 FK。在 Item 中，ParentID 可以引用另一个 Item 或父对象。

我想做的是能够从 Item 关系中的一个叶节点一直迭代到它的父节点，直到我最终可以确定给定 Item 叶节点从哪个 ObjectID 派生。

我想在 SQL 中执行此操作。我正在使用 SQL Server 2008。

这是我的想法。我可以遍历 Item ParentID 直到我不能再将 ParentID 与另一个 Item 连接起来。这个ParentID就是我要返回的ObjectID。

我一直在尝试使用内部联接让它工作，但没有成功。我正在使用 C#，所以如果这可以在 linq 中完成，我不怀疑它可以在没有非常低效率的情况下完成，那也很好。

我的回答是:

WITH ObjectH (ParentID, ItemID, Level) AS
(
   -- Base case
   SELECT
      ParentID,
      ItemID,
      1 as Level 
   FROM Item
   WHERE ItemID = @param 

   UNION ALL

   -- Recursive step
   SELECT
      i.ParentID,
      i.ItemID,
      oh.Level + 1 AS Level
   FROM Item i
      INNER JOIN ObjectH oh ON
         c.ItemID = oh.ParentID           
)

SELECT TOP 1 ParentID
FROM ObjectH
ORDER BY Level DESC

这有效。

Answer 1

我尝试使用递归 SQL Server CTE 来执行此操作。

从逻辑上讲，这就是 CTE 的工作方式，这应该是这样，但我从未将它用于与您完全一样的模式。

通常 SQL 中的树结构没有额外的父“对象”表。 (似乎您也在数据库中精确地 1:1 模仿您的对象设计？如果我是您，我会重新审视该模式)

尝试一下，如果有效请告诉我。

编辑:稍微更改了基本查询条件

EDIT2:更改查询以使其特定于一项

WITH ObjectHierarchy (ItemID, Name, ParentID, Level) AS
(
   SELECT 
      ItemID, 
      Name, 
      ParentID, 
      1 as Level 
   FROM Item it, Object ob
   WHERE it.ParentID = ob.ObjectID
   AND ob.ItemID = @itemIdToBeSearched

   UNION ALL

   SELECT
      i.ItemID,
      i.Name,
      i.ParentID,
      oh.Level + 1 AS Level
   FROM Item i
   INNER JOIN ObjectHierarchy oh ON
      i.ParentID = oh.ItemID
   AND oh.ItemID = @itemIdToBeSearched

)

SELECT parentID
FROM ObjectHierarchy 
WHERE LEVEL = 1