通过“链表”迭代在一个SQL查询？ [英] Iterate through "linked list" in one SQL query?

问题描述

我的表看起来基本上是这样的：

I have a table that looks basically like this:

id | redirectid | data

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

where the redirectid is an id to another row. Basically if a row is selected, and it has a redirectid, then the redirectid data should be used in it's place. There may be multiple redirects until redirectid is NULL. Essentially these redirects form a linked list in the table. What I'd like to know is, given an id, is it possible to set up a sql query that will iterate through all the possible redirects and return the id at the end of the "list"?

这是使用Postgresql 8.3，如果可能的话，我想在sql查询中做一切（而不是在我的代码中迭代）。

This is using Postgresql 8.3 and I'd like to do everything in on sql query if possible (rather than iterate in my code).

推荐答案

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

Does postgresql support recursive queries that use WITH clauses? If so, something like this might work. (If you want a tested answer, provide some CREATE TABLE and INSERT statements in your question, along with the results you need for the sample data in the INSERTs.)

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语法，所以这有点伪：

:( You can implement the recursion yourself based on the WITH expression. I don't know postgresql syntax for sequential programming, so this is a bit pseudo:

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

Insert the result of this query into a new table called 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

同时声明一个integer :: depth并将其初始化为零。然后重复跟随它直到它不再向Links添加行。链接将包含你的结果。

Also declare an integer ::depth and initialize it to zero. Then repeat the following until it no longer adds rows to Links. Links will then contain your result.

  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;

我认为这比任何光标解决方案都要好。事实上，我根本无法想到游标如何对这个问题有用。

I think this will be better than any cursor solution. In fact, I can't really think of how cursors would be useful for this problem at all.

请注意，如果有任何循环，这将不会终止（重定向到最终是通告）。

Note that this will not terminate if there are any cycles (redirects that are ultimately circular).

这篇关于通过“链表”迭代在一个SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！