sql - Postgresql 递归自连接

Question

我在 postgres 中的表如下所示，表存储 ID 之间的链式关系，我希望有一个查询可以产生类似“vc1”->“rc7”或“vc3”->“rc7”的结果，我只会查询第一列 ID1 中的 ID

ID1     ID2
"vc1"   "vc2"
"vc2"   "vc3"
"vc3"   "vc4"
"vc4"   "rc7"

所以我想在这里提供一些“头”ID，我必须为此获取尾部(链中的最后一个)ID。

Answer 1

这是简单递归公用表表达式 ( WITH RECURSIVE ) 的经典用法，在 PostgreSQL 8.4 及更高版本中可用。

此处演示:http://sqlfiddle.com/#!12/78e15/9

给定示例数据作为 SQL:

CREATE TABLE Table1
    ("ID1" text, "ID2" text)
;

INSERT INTO Table1
    ("ID1", "ID2")
VALUES
    ('vc1', 'vc2'),
    ('vc2', 'vc3'),
    ('vc3', 'vc4'),
    ('vc4', 'rc7')
;

你可以这样写:

WITH RECURSIVE chain(from_id, to_id) AS (
  SELECT NULL, 'vc2'
  UNION
  SELECT c.to_id, t."ID2"
  FROM chain c
  LEFT OUTER JOIN Table1 t ON (t."ID1" = to_id)
  WHERE c.to_id IS NOT NULL
)
SELECT from_id FROM chain WHERE to_id IS NULL;

这样做是迭代遍历链，将每一行添加到 chain表作为从和到指针。当它遇到不存在“to”引用的行时，它将为该行添加一个空的“to”引用。下一次迭代会注意到“to”引用为空并生成零行，这会导致迭代结束。

然后，外部查询通过不存在的 to_id 选择已确定为链末端的行。

了解递归 CTE 需要一些努力。他们需要了解的关键事项是:

• 它们从初始查询的输出开始，重复将其与“递归部分”(UNION 或 UNION ALL 之后的查询)的输出合并，直到递归部分不添加任何行。这会停止迭代。

• 它们并不是真正的递归，更多的是迭代，尽管它们适用于您可能会使用递归的各种事情。

所以您基本上是在循环中构建一个表。您不能删除行或更改它们，只能添加新行，因此您通常需要一个外部查询来过滤结果以获得您想要的结果行。您通常会添加额外的列，其中包含用于跟踪迭代状态、控制停止条件等的中间数据。

查看未过滤的结果会有所帮助。如果我用一个简单的 SELECT * FROM chain 替换最终的摘要查询我可以看到生成的表格:

 from_id | to_id 
---------+-------
         | vc2
 vc2     | vc3
 vc3     | vc4
 vc4     | rc7
 rc7     | 
(5 rows)

第一行是手动添加的起点行，您可以在其中指定要查找的内容 - 在本例中为 vc2 . UNION 添加了后续的每一行ed 递归项，执行 LEFT OUTER JOIN基于先前的结果并返回一组新行，这些行与先前的 to_id 配对(现在在 from_id 列)到下一个 to_id .如果LEFT OUTER JOIN与to_id 不匹配将为空，导致下一次调用立即返回行并结束迭代。

因为此查询并非每次都尝试只添加最后 行，所以它实际上在每次迭代中重复了相当多的工作。为避免这种情况，您需要使用更像 Gordon 的方法，但在扫描输入表时还要对前一个深度字段进行过滤，因此您只加入了最近的行。在实践中，这通常不是必需的，但对于非常大的数据集或您无法创建适当索引的情况，这可能是一个问题。

更多可以在the PostgreSQL documentation on CTEs中了解.