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

I have a table similar to this:

我有一张类似的桌子:

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

I can use the parentid field to arrange data into a tree structure.

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

Now here's the bit I can't work out. Given a parentid, is it possible to write an SQL statement to add up all the value fields under that parentid and recurse down the branch of the tree ?

这是我不能解决的问题。给定一个parentid，是否有可能编写一个SQL语句来添加该parentid下的所有值字段并递归到树的分支?

UPDATE: I'm using posgreSQL so the fancy MS-SQL features are not available to me. In any case, I'd like this to be treated as a generic SQL question.

更新:我正在使用posgreSQL，所以我不能使用复杂的MS-SQL特性。在任何情况下，我希望将其视为一个通用的SQL问题。

BTW, I'm very impressed to have 6 answers within 15 minutes of asking the question! Go stack overflow!

顺便说一句，我对在提问的15分钟内得到6个答案印象深刻!堆栈溢出!

14 个解决方案

#1

There are a few ways to do what you need in PostgreSQL.

有几种方法可以在PostgreSQL中完成所需的工作。

If you can install modules, look at the tablefunc contrib. It has a connectby() function that handles traversing trees. http://www.postgresql.org/docs/8.3/interactive/tablefunc.html

如果可以安装模块，请查看tablefunc设计。它有一个connectby()函数，用于处理遍历树。http://www.postgresql.org/docs/8.3/interactive/tablefunc.html
Also check out the ltree contrib, which you could adapt your table to use: http://www.postgresql.org/docs/8.3/interactive/ltree.html

还可以查看ltree后悔器，您可以将表调整为使用:http://www.postgresql.org/docs/8.3/interactive/ltree.html
Or you can traverse the tree yourself with a PL/PGSQL function.

或者您可以使用PL/PGSQL函数遍历树。

Something like this:

是这样的:

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

#2

Here is an example script using common table expression:

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

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

The script above creates a 'virtual' table called sumthis that has columns id and val. It is defined as the result of two selects merged with union all.

上面的脚本创建了一个名为sumthis的“虚拟”表，该表具有列id和val，定义为两个选择与union all合并的结果。

First select gets the root (where id = :selectedid).

首先选择获取根(其中id =:selectedid)。

Second select follows the children of the previous results iteratively until there is nothing to return.

Second select将迭代地跟随前一个结果的子结果，直到没有返回。

The end result can then be processed like a normal table. In this case the val column is summed.

然后，可以像正常表一样处理最终结果。在这种情况下，对val列进行求和。

#3

Since version 8.4, PostgreSQL has recursive query support for common table expressions using the SQL standard WITH syntax.

由于版本8.4,PostgreSQL对常用的表表达式有递归查询支持，使用的是带有语法的SQL标准。

#4

If you want a portable solution that will work on any ANSI SQL-92 RDBMS, you will need to add a new column to your table.

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

Joe Celko is the original author of the Nested Sets approach to storing hierarchies in SQL. You can Google "nested sets" hierarchy to understand more about the background.

Joe Celko是嵌套集合方法的原始作者，用于在SQL中存储层次结构。您可以谷歌“嵌套集”层次结构来了解更多关于背景的信息。

Or you can just rename parentid to leftid and add a rightid.

或者你可以将parentid重命名为leftid并添加一个右tid。

Here is my attempt to summarize Nested Sets, which will fall woefully short because I'm no Joe Celko: SQL is a set-based language, and the adjacency model (storing parent ID) is NOT a set-based representation of a hierarchy. Therefore there is no pure set-based method to query an adjacency schema.

下面是我总结嵌套集的尝试，因为我不是Joe Celko: SQL是一种基于集合的语言，邻接模型(存储父ID)不是基于集合的层次结构表示。因此，没有基于集合的方法来查询邻接模式。

However, most of the major platforms have introduced extensions in recent years to deal with this precise problem. So if someone replies with a Postgres-specific solution, use that by all means.

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

#5

A standard way to make a recursive query in SQL are recursive CTE. PostgreSQL supports them since 8.4.

在SQL中进行递归查询的标准方法是递归CTE。PostgreSQL从8.4开始支持它们。

In earlier versions, you can write a recursive set-returning function:

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

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)

See this article:

看到这篇文章:

Hierarchical queries in PostgreSQL
分级查询在PostgreSQL

#6

If your using SQL Server 2005, there is a really cool way to do this using Common Table Expressions.

如果您使用的是SQL Server 2005，那么有一种非常酷的方法可以使用公共表表达式来实现这一点。

It takes all of the gruntwork out of creating a temporary table, and basicly allows you to do it all with just a WITH and a UNION.

它将所有的垃圾工作从创建一个临时的桌子，并基本上允许你做所有的与和一个联合。

Here is a good tutorial:

这里有一个很好的教程:

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

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

#7

use a common table expression.

使用公共表表达式。

May want to indicate this is SQL Server 2005 or above only. Dale Ragan

可能需要指出这是SQL Server 2005或以上。戴尔Ragan

here's an article on recursion by SqlTeam without common table expressions.

这里有一篇关于SqlTeam递归的文章，没有通用的表表达式。

#8

The following code compiles and it's tested OK.

下面的代码编译并测试OK。

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';

The condition "child <> parent" is needed in my case because nodes point to themselves.

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

Have fun :)

玩得开心:)

#9

Oracle has "START WITH" and "CONNECT BY"

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

#10

Just as a brief aside although the question has been answered very well, it should be noted that if we treat this as a:

简单地说，虽然这个问题已经得到了很好的回答，但应该指出的是，如果我们把这个问题当作:

generic SQL question

通用SQL问题

then the SQL implementation is fairly straight-forward, as SQL'99 allows linear recursion in the specification (although I believe no RDBMSs implement the standard fully) through the WITH RECURSIVE statement. So from a theoretical perspective we can do this right now.

然后SQL实现相当直接，因为SQL'99允许在规范中使用线性递归(尽管我认为没有rdbms完全实现这个标准)通过WITH recursive语句。所以从理论的角度来看，我们现在可以做这个。

#11

None of the examples worked OK for me so I've fixed it like this:

没有一个例子对我有效，所以我这样修正:

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;

#12

is this SQL Server? Couldn't you write a TSQL stored procedure that loops through and unions the results together?

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

I am also interested if there is a SQL-only way of doing this though. From the bits I remember from my geographic databases class, there should be.

我还对是否有一种只使用sql的方法感兴趣。从我对地理数据库类的记忆中，应该有。

#13

I think it is easier in SQL 2008 with HierarchyID

我认为在SQL 2008中使用层次化id更容易

#14

-1

If you need to store arbitrary graphs, not just hierarchies, you could push Postgres to the side and try a graph database such as AllegroGraph:

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

Everything in the graph database is stored as a triple (source node, edge, target node) and it gives you first class support for manipulating the graph structure and querying it using a SQL like language.

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

It doesn't integrate well with something like Hibernate or Django ORM but if you are serious about graph structures (not just hierarchies like the Nested Set model gives you) check it out.

它不能很好地与Hibernate或Django ORM之类的东西集成，但是如果您认真对待图形结构(不只是像嵌套模型那样的层次结构)，就可以检查它。

I also believe Oracle has finally added a support for real Graphs in their latest products, but I'm amazed it's taken so long, lots of problems could benefit from this model.

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

#1