是否可以进行递归SQL查询?

在PostgreSQL中,有几种方法可以满足您的需要。

• 如果可以安装模块,请查看tablefunc contrib。它有一个connectby()函数来处理遍历树。 http://www.postgresql.org/docs/8.3/interactive/tablefunc.html

• 还可以查看ltree contrib,它可以使您的表适应使用: http://www.postgresql.org/docs/8.3/interactive/ltree.html

• 也可以使用pl/pgsql函数自己遍历树。

像这样:

create or replace function example_subtree (integer)
returns setof example as
'declare results record;
         child record;
 begin
  select into results * from example where parent_id = $1;
  if found then
    return next results;
    for child in select id from example
                  where parent_id = $1
      loop
        for temp in select * from example_subtree(child.id)
        loop
          return next temp;
        end loop;
      end loop;
  end if;
  return null;
end;' language 'plpgsql';

select sum(value) as value_sum
  from example_subtree(1234);

下面是使用公用表表达式的示例脚本:

with recursive sumthis(id, val) as (
    select id, value
    from example
    where id = :selectedid
    union all
    select C.id, C.value
    from sumthis P
    inner join example C on P.id = C.parentid
)
select sum(val) from sumthis

上面的脚本创建一个名为 sumthis 有柱子的 id 和 val . 它定义为两个选择合并后的结果 union all .

弗斯特 select 获取根目录( where id = :selectedid )

第二 选择 迭代地跟踪前面结果的子级,直到没有要返回的内容为止。

然后,可以像处理普通表一样处理最终结果。在这种情况下,VAL列是求和的。

自8.4版以来,PostgreSQL已经 recursive query support 对于使用SQL标准的公用表表达式 WITH 语法。

如果您想要一个可移植的解决方案,可以在任何ANSI上使用 SQL-92 RDBMS,您将需要向表中添加一个新列。

Joe Celko是 Nested Sets 在SQL中存储层次结构的方法。你可以谷歌 "nested sets" hierarchy 了解更多关于背景的信息。

或者只需将parentID重命名为 左撇子 添加一个 右派 .

下面是我对嵌套集的总结,这将非常简短,因为我不是Joe Celko:SQL是一种基于集的语言,相邻模型(存储父ID)不是层次结构的基于集的表示。因此,没有纯粹的基于集合的方法来查询邻接模式。

然而 近年来,大多数主要平台都引入了扩展来处理这个精确的问题。所以,如果有人用postgres特定的解决方案来回复,那么一定要使用它。

在中进行递归查询的标准方法 SQL 是递归的 CTE . PostgreSQL 支持他们,因为 8.4 .

在早期版本中,可以编写递归集返回函数:

CREATE FUNCTION fn_hierarchy (parent INT)
RETURNS SETOF example
AS
$$
        SELECT  example
        FROM    example
        WHERE   id = $1
        UNION ALL
        SELECT  fn_hierarchy(id)
        FROM    example
        WHERE   parentid = $1
$$
LANGUAGE 'sql';

SELECT  *
FROM    fn_hierarchy(1)

请参阅本文:

• Hierarchical queries in PostgreSQL

如果您使用的是SQL Server 2005,那么使用公共表表达式是一种非常酷的方法。

创建一个临时表需要完成所有的工作,而且基本上只需要一个with和一个union就可以完成所有的工作。

以下是一个很好的教程:

http://searchwindevelopment.techtarget.com/tip/0,289483,sid8_gci1278207,00.html

使用A common table expression .

可能只想表明这是SQL Server 2005或更高版本。 Dale Ragan

here's an article 在没有常用表表达式的情况下由sqlteam递归。

下面的代码编译,测试结果正常。

create or replace function subtree (bigint)
returns setof example as $$
declare
    results record;
    entry   record;
    recs    record;
begin
    select into results * from example where parent = $1;
    if found then
        for entry in select child from example where parent = $1 and child  parent loop
            for recs in select * from subtree(entry.child) loop
                return next recs;
            end loop;
        end loop;
    end if;
    return next results;
end;
$$ language 'plpgsql';

在我的例子中,需要条件“child<gt;parent”,因为节点指向它们自己。

玩得开心:

Oracle有“Start with”和“Connect by”

select 
    lpad(' ',2*(level-1)) || to_char(child) s

from 
    test_connect_by 

start with parent is null
connect by prior child = parent;

http://www.adp-gmbh.ch/ora/sql/connect_by.html

尽管这个问题已经得到了很好的回答,但要注意的是,如果我们将其视为:

一般性SQL问题

然后,SQL实现是相当直接的,因为SQL'99允许在规范中进行线性递归(尽管我相信没有RDBMS完全实现标准),通过 WITH RECURSIVE 语句。所以从理论上讲,我们现在就可以做到这一点。

没有一个例子对我有用,所以我把它改成这样:

declare
    results record;
    entry   record;
    recs    record;
begin
    for results in select * from project where pid = $1 loop
        return next results;
        for recs in select * from project_subtree(results.id) loop
            return next recs;
        end loop;
    end loop;
    return;
end;

这是SQL Server吗?您不能编写一个TSQL存储过程来循环并将结果联合在一起吗?

不过,如果有一种只有SQL的方法可以做到这一点,我也很感兴趣。从我在地理数据库类中记得的片段来看,应该是这样的。

我认为在SQL 2008中使用 HierarchyID

如果您需要存储任意图形,而不仅仅是层次结构,您可以将Postgres推到一边,并尝试使用图形数据库,例如 AllegroGraph :

图形数据库中的所有内容都存储为三重结构(源节点、边缘节点、目标节点),它为您提供了一流的支持,支持您操作图形结构并使用类似SQL的语言进行查询。

它不能很好地与Hibernate或Django ORM之类的东西集成,但是如果您认真对待图形结构(不仅仅是嵌套集模型提供的层次结构),请检查它。

我也相信甲骨文最终在他们最新的产品中增加了对真实图形的支持,但我很惊讶它花了这么长时间,很多问题都可以从这个模型中获益。