代码之家 › 专栏 › 技术社区 › Niki Yoshiuchi

在一个SQL查询中迭代“链表”?

iteration postgresql sql

Niki Yoshiuchi · 技术社区 · 16 年前

我有一张桌子,基本上是这样的:

id | redirectid | data

其中重定向ID是指向另一行的ID。基本上,如果选择了一行,并且它有一个重定向ID,那么重定向ID数据应该在它的位置上使用。在RedirectID为空之前,可能存在多个重定向。本质上,这些重定向在表中形成一个链接列表。我想知道的是,给定一个ID,是否可以设置一个SQL查询,该查询将迭代所有可能的重定向,并在“列表”的末尾返回ID?

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

2 回复 | 直到 7 年前

Steve Kass 16 年前

PostgreSQL是否支持与子句一起使用的递归查询?如果是这样,这样的事情可能会奏效。(如果您想要一个经过测试的答案,请在您的问题中提供一些create table和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;

我认为这比任何光标解决方案都好。事实上,我真的想不出光标对这个问题有什么帮助。

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

gustavohenke Fernando Lubianco 9 年前

我想你应该创造一个 user-defined function 在这个脉络中:

create function FindLastId (ID as integer) returns integer as $$
    declare newid integer;
    declare primaryid integer;
    declare continue boolean;
    begin
        set continue = true;
        set primaryid = $1;
        while (continue)
            select into newid redirectid from table where id = :primaryid;

            if newid is null then
                set continue = false;
            else
                set primaryid = :newid;
            end if;
        end loop;

        return primaryid;
    end;
    $$ language pgplsql;

我对Postgres语法有点动摇,所以您可能需要做一些清理工作。无论如何,您可以这样调用函数:

select id, FindLastId(id) as EndId from table

在这样的桌子上:

id     redirectid    data
1          3          ab
2        null         cd
3          2          ef
4          1          gh
5        null         ij

这将返回:

id    EndId
1       2
2       2
3       2
4       2
5       5

请注意,这将明显变慢,但对于索引良好的表上的一个小结果集,它应该可以很快地获得ID。