sql - 在一个 SQL 查询中遍历 "linked list"？

Question

我有一个基本上看起来像这样的表:

id | redirectid | data

redirectid 是另一行的 id。基本上，如果选择了一行，并且它有一个 redirectid，那么应该在它的位置使用 redirectid 数据。可能会有多次重定向，直到 redirectid 为 NULL。本质上，这些重定向在表中形成了一个链表。我想知道的是，给定一个 id，是否可以设置一个 sql 查询来遍历所有可能的重定向并返回“列表”末尾的 id？

这是使用 Postgresql 8.3，如果可能的话，我想在 sql 查询中做所有事情(而不是在我的代码中迭代)。

Answer 1

postgresql 是否支持使用 WITH 子句的递归查询？如果是这样，这样的事情可能会奏效。 (如果您想要经过测试的答案，请在问题中提供一些 CREATE TABLE 和 INSERT 语句，以及 INSERT 中示例数据所需的结果。)

with Links(id,link,data) as (
  select
    id, redirectid, data
  from T
  where redirectid is null
  union all
  select
    id, redirectid, null
  from T
  where redirectid is not null
  union all
  select
    Links.id,
    T.redirectid,
    case when T.redirectid is null then T.data else null end
  from T
  join Links
  on Links.link = T.id
)
  select id, data
  from Links
  where data is not null;

补充说明:

:( 你可以根据WITH表达式自己实现递归，我不懂顺序编程的postgresql语法，所以有点伪:

将此查询的结果插入到名为 Links 的新表中:

select
    id, redirectid as link, data, 0 as depth
  from T
  where redirectid is null
  union all
  select
    id, redirectid, null, 0
  from T
  where redirectid is not null

同时声明一个整数::depth 并将其初始化为零。然后重复以下操作，直到它不再向 Links 添加行。然后链接将包含您的结果。

  increment ::depth;
  insert into Links
  select
    Links.id,
    T.redirectid,
    case when T.redirectid is null then T.data else null end,
    depth + 1
  from T join Links
  on Links.link = T.id
  where depth = ::depth-1;
end;

我认为这比任何游标解决方案都要好。事实上，我真的想不出游标对这个问题有什么用。

请注意，如果存在任何循环(最终循环的重定向)，这将不会终止。