从自关联表中获取父级的SQL

Question

我有一个类似这样的表格：

Item
{
   int ItemID
   int ParentID 
   string Name
}

“Item”实际上是较大的“Object”表中的一个子集：

Object
{
    int ObjectID
    string Color
}

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

我想做的是能够从 Item 关系中的叶子迭代到其所有父级，直到最终确定给定 Item 叶子所属的 ObjectID。

我想在 SQL 中完成这个操作。我正在使用 SQL Server 2008。

我的想法是：只需通过 Item 的 ParentID 向上迭代，直到无法将 ParentID 与另一个 Item 连接起来为止。这个 ParentID 就是我要返回的 ObjectID。

我一直在尝试使用内部连接来实现这一点，但没有成功。如果可以使用 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

这个有效。

- George

Answer 1

我尝试使用递归SQL Server CTE来实现这个功能。

从逻辑上讲，CTE是如何工作的，理论上应该可以实现，但我从未像你这样使用过这种模式的架构。

通常，在SQL中，树形结构不会有额外的父级“Object”表。（看起来你在数据库中完全模仿了对象的设计？如果我是你，我会重新考虑这种模式）

尝试一下，然后告诉我它是否有效。

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

编辑2：更改查询以使其针对一个项目具体化

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

- Jagmag

---

代码可以运行，它列出了所有的关系，但是我如何根据任何ItemID选择ObjectID呢？ - George

该列表包含不止一个Level=1。它列出了每个可能的Level=1。我一直在研究它，据我所知，它应该可以工作。 - George

你上次的更新没有起作用，不过我最终弄好了。我从 SQL 中删除了该对象，并在我的问题中发布了可运行的代码。也许你可以帮忙解决 Top 1 的需求。至少现在我有了可用的东西，可以做我实际需要做的事情了。感谢你的帮助。 - George

我解决了排名第一的问题，然后我又编辑了我的答案。它非常接近你所需要的。非常感谢！ - George

最终编辑。让它正常工作了。更新了代码。实际上需要选择最大级别，这就是为什么有倒序排列和选择前1个的原因。再次感谢。 - George

Answer 2

首先，允许ParentID列作为“外键”引用两个不同的表格是可怕的设计。由于您在编写此查询时遇到的困难，您可能已经意识到了这一点。

话虽如此，假设您已经无法重新设计模式，我会通过创建一个表示Item应该如何的视图来解决这个问题，然后在该视图上使用传统的递归CTE方法来遍历层次结构。

create view vwItem 
as
select i.ItemID, 
       case when o.ObjectID is null then i.ParentID else null end as ParentID, 
       o.ObjectID, Name
    from Item i
        left join Object o
            on i.ParentID = o.ObjectID
go

;with cteItemList as (
    select i.ItemID, i.ParentID, i.ObjectID, i.Name, 1 as Level
        from vwItem i
        where i.ParentID is null
    union all
    select i.ItemID, i.ParentID, i.ObjectID, i.Name, il.Level+1 as Level
        from vwItem i
            inner join cteItemList il
                on i.ParentID = il.ItemID
)
select *
    from cteItemList
    order by Level, ItemID

- Joe Stefanelli

---

完全同意你的观点，很高兴你认为CTE是传统的！ - MikeAinOz

Answer 3

只需在SQL中创建一个标量函数来迭代，然后将其包含在一个视图中，该视图模拟您的表格但将函数结果添加为一列。然后您可以基于该视图构建LINQ对象，并在代码中获取ParentID。

- sliderhouserules