时间戳

首页 > 解决方案 > 在自我参照表中找到最顶层的父母

sql - 在自我参照表中找到最顶层的父母

问题描述

给定多个 ID，每个 ID 都可能连接到同一张表中的其他条目，我想找到它们的顶级父级。即父 ID 为的那些行NULL。因此，在每个红色单元下方，都会将层次结构连接到相应的绿色单元。

等级制度

在一个非常相似的问题中借用这个答案，这是一个玩具模式。

DECLARE @t TABLE (ID INT, link INT)

INSERT INTO @t VALUES
(1, NULL),
(2, 1),
(3, 2),
(4, 3),
(5, 3),
(6, 2),
(7, 1),

(8, NULL),
(9, 8),
(10, 9),
(11, 9),
(12, 9),
(13, 12),
(14, 12),
(15, 8);

对于具有两个底层节点的示例，我将有一组 ID 6 和 13。然后我想要一个像 (6, 1) 和 (13, 8) 这样的结果集。为了构建每个链接，答案建议使用公共表表达式。

WITH cte AS (
    SELECT ID, link
    FROM @t
    WHERE ID IN (6, 13)

    UNION ALL

    SELECT t.ID, t.link
    FROM @t t
    JOIN cte c ON t.ID = c.link
    WHERE t.link IS NOT NULL
)

SELECT *
FROM cte

这产生了这个结果：

 ID | link
----+------
  6 |   2
 13 |  12
 12 |   9
  9 |   8
  2 |   1

但是，我不确定如何将其组合成每个起点的结果。对于一个 ID，我也许可以选择结果集的最后一行并获取链接 ID，但不能用于多个。请注意，自然可以有多个顶级父级（尽管分支只发生向下，因此给定节点只有一个父级），也可以选择中级条目作为起点。

而不是UNION ALL我天真地尝试过JOIN，但事实证明这样的 CTE 是不允许的。

以下是上面所有的红色节点：(3, 6, 11, 13, 15). 他们应该映射到(1, 1, 8, 8, 8).

标签： sqlsql-server

解决方案

代码的2个问题：

  1. 你需要通过递归来追踪你的起始ID；
  2. 递归部分中的where条件实际上是在阻止您获得结果。

像这样：

WITH cte AS (
    SELECT ID, link, ID as [StartID]
    FROM @t
    WHERE ID IN (6, 7)
    UNION ALL
    SELECT t.ID, t.link, c.StartID
    FROM @t t
    JOIN cte c ON t.ID = c.link
)
SELECT c.StartID, c.ID
FROM cte c
where c.link is null;

推荐阅读