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

Question

我有一个与此类似的表：

CREATE TABLE example (
  id integer primary key,
  name char(200),
  parentid integer,
  value integer);

我可以使用parentid 字段将数据排列成树结构。

现在这是我无法解决的一点。 给定一个parentid，是否可以编写一条SQL语句来累加该parentid下的所有值字段并向下递归树的分支？

更新：我正在使用 posgreSQL，因此我无法使用精美的 MS-SQL 功能。 无论如何，我希望将其视为一个通用的 SQL 问题。

Answer 1

在 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)

请参阅本文：

• PostgreSQL 中的分层查询

Answer 2

以下代码可以编译并测试正常。

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 <>parent”，因为节点指向它们自己。

Answer 3

如果您想要一个可在任何 ANSI SQL-92 RDBMS 上运行的便携式解决方案，您将需要向表中添加新列。

Joe Celko 是嵌套集方法的原始作者在 SQL 中存储层次结构。 您可以通过 Google “嵌套集”层次结构了解有关的背景。

或者您可以将parentid重命名为leftid并添加rightid。

这是我尝试总结嵌套集的尝试，但它会严重不足，因为我不是 Joe Celko：SQL 是一种基于集合的语言，并且邻接模型（存储父 ID）不是层次结构的基于集合的表示。 因此，不存在纯粹的基于集合的方法来查询邻接模式。

但是，大​​多数主要平台近年来都引入了扩展来解决这个具体问题。 因此，如果有人回复 Postgres 特定的解决方案，请务必使用它。

Answer 4

从 8.4 版本开始，PostgreSQL 为公共表表达式提供了递归查询支持使用 SQL 标准 WITH 语法。

Answer 5

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

我也很感兴趣是否有一种仅 SQL 的方法可以做到这一点。 从我在地理数据库课上的记忆来看，应该有。

Answer 6

这些示例对我来说都不起作用，所以我像这样修复了它：

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;

Answer 7

顺便说一句，尽管这个问题已经得到很好的回答，但应该指出的是，如果我们将其视为：

通用 SQL 问题

，那么 SQL 实现相当简单，因为 SQL'99 允许通过 WITH RECURSIVE 语句在规范中进行线性递归（尽管我相信没有 RDBMS 完全实现该标准）。 所以从理论角度来看我们现在就可以做到这一点。

Answer 8

Oracle 有“开始”和“连接”

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

Answer 9

下面是一个使用通用表表达式的示例脚本：

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）。

第二个 select 迭代地跟踪先前结果的子项，直到没有任何内容可返回。

然后可以像普通表一样处理最终结果。 在这种情况下，val 列被求和。

Answer 10

有几种方法可以在 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);

Answer 11

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

图形数据库中的所有内容都存储为三元组（源节点、边缘、目标节点），它为操作图形结构并使用类似 SQL 的语言查询它提供了一流的支持。

它与 Hibernate 或 Django ORM 之类的东西集成得不好，但如果您认真对待图形结构（不仅仅是像 Nested Set 模型给您的层次结构），请检查一下。

我还相信 Oracle 最终在其最新产品中添加了对真实图形的支持，但我很惊讶它花了这么长时间，很多问题都可以从这个模型中受益。