基于子ID获取父级的SQL查询

Question

在 Microsoft SQL 环境中，我有以下情况：

CREATE TABLE grps
(
  [id] varchar(50),
  [parentid] varchar(50),
  [value] varchar(50)
);

INSERT INTO grps
    ([id], [parentid], [value])
VALUES
    ('-5001', '0', null),
    ('-5002', '-5001', null),
    ('-5003', '-5002', '50'),
    ('-5004', '-5003', null),
    ('-5005', '0', null),
    ('-5006', '0', null),
    ('-5007', '0', null),
    ('-5008', '-5006', null);

我试图基于一个子项的id获取父项。如果查询的id是最后一个父项，则应仅返回最后一项。

例如：

如果我查询：id = '-5004'，它应该返回('-5004', '-5003', null), ('-5003', '-5002', '50'), ('-5002', '-5001', null), ('-5001', '0', null)。

如果我查询id = '-5007'，它应该返回('-5007', '0', null)。

如果能按照树形结构列出查询的id和其余的内容，那就太棒了。

我尝试过几种不同的CTE方法，但不幸的是都没有成功。所以我在这里寻求帮助或想法。

先谢谢了。

- Gurby

Answer 1

你的想法很正确，可以使用递归公共表表达式来实现！以下是递归 CTE 的样例代码：

DECLARE @ID varchar(50) = '5004';

WITH CTE AS
(
    --This is called once to get the minimum and maximum values
    SELECT id, parentid, value
    FROM grps
    WHERE id= @ID
    UNION ALL
    --This is called multiple times until the condition is met
    SELECT g.id, g.parentid, g.value
    FROM CTE c, grps g
    WHERE g.id= c.parentid
    --If you don't like commas between tables then you can replace the 2nd select 
    --statement with this:
    --SELECT g.id, g.parentid, g.value
    --FROM CTE c
    --INNER JOIN grps g ON g.id= c.parentid
    --This can also be written with CROSS JOINS! 
    --Even though it looks more like another way of writing INNER JOINs.
    --SELECT g.id, g.parentid, g.value
    --FROM CTE c
    --CROSS JOIN grps g
    --WHERE g.id = c.parentid
)

SELECT * FROM CTE

请注意，最大递归深度为100，除非您在最后一个选择语句的末尾添加option (maxrecursion 0)。数字0表示无限制，但您也可以将其设置为任何其他值。

祝使用愉快！

- John Odom

---

谢谢！这正是我在寻找的。当我获得声望时，我会给你点赞。 - Gurby

@Gurby 不用谢！ :) - John Odom

Answer 2

我正在努力推广hierarchyid技术。首先，让我们来看一下设置：

CREATE TABLE grps
(
  [id] varchar(50),
  [parentid] varchar(50),
  [value] varchar(50),
  h HIERARCHYID NULL
);

SELECT * FROM grps
INSERT INTO grps
    ([id], [parentid], [value])
VALUES
    ('-5001', '0', null),
    ('-5002', '-5001', null),
    ('-5003', '-5002', '50'),
    ('-5004', '-5003', null),
    ('-5005', '0', null),
    ('-5006', '0', null),
    ('-5007', '0', null),
    ('-5008', '-5006', null);

WITH cte AS (
    SELECT id ,
           parentid ,
           value ,
           CAST('/' + id + '/' AS nvarchar(max)) AS h
    FROM grps
    WHERE parentid = 0

    UNION ALL

    SELECT child.id ,
           child.parentid ,
           child.value ,
           CAST(parent.h + child.id + '/' AS NVARCHAR(MAX)) AS h
    FROM cte AS [parent]
    JOIN grps AS [child]
        ON child.parentid = parent.id
)
UPDATE g
SET h = c.h
FROM grps AS g
JOIN cte AS c
    ON c.id = g.id

我在这里做的只是向您的表定义中添加了一个层次结构列，并计算了它的值。为了回答您最初的问题，现在它看起来像这样：

SELECT g.id ,
       g.parentid ,
       g.value ,
       g.h.ToString() 
FROM dbo.grps AS g
JOIN grps AS c
    ON c.h.IsDescendantOf(g.h) = 1
WHERE c.id = '-5004'

为了提高性能，你应该分别对id和h列建立索引（即在不同的索引中）。

另外，有几点需要注意：

- 当数据看起来是数字时，将id列设置为varchar类型可能会让人怀疑，更重要的是这样做效率很低。如果是我，我会使用int类型。但也许你的实际数据比较混乱（例如有像'A1234'这样的id）。 - 我还会使用NULL代替0作为parentid表示顶级成员（即没有父级的成员）。但这更多是个人选择，而非真正的性能影响。

- Ben Thul

---

拥有hierarchyID的好处是什么？ - John Odom

非常好的问题。因为你将层次结构存储在行中，所以不必在运行时计算它。因此，如果你的工作负载是读取为主，这可以节省很多时间，因为你只需要计算一次层次结构。 - Ben Thul

此外，使用递归CTE的方式，每次都会触及到每一行。而使用这种方法，您应该能够仅寻找相关的行。 - Ben Thul

不错！我甚至不知道还有 HIERARCHYID 数据类型。 - John Odom

大多数人不这样做。这就是为什么我要传道。 :) 现在我想起来了，我有几篇博客要写。 - Ben Thul