在一个SQL查询中遍历“ inked list”？

Question

我有一张基本上看起来像这样的表：

id | redirectid | data

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

这使用的是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并将其初始化为零。然后重复以下操作，直到不再向“链接”添加行。链接将包含您的结果。

  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;

我认为这将比任何游标解决方案都更好。实际上，我根本无法想到游标对这个问题有多大帮助。

请注意，如果有任何循环（重定向最终是循环的），则此操作不会终止。