是否可以进行递归 SQL 查询? [英] Is it possible to make a recursive SQL query?

问题描述

我有一个类似的表格:

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

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

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

现在有一点我无法解决.给定一个parentid，是否可以编写一条SQL语句将该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 ?

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

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.

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

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

推荐答案

在 PostgreSQL 中有几种方法可以满足您的需求.

There are a few ways to do what you need in 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

Also check out the ltree contrib, which you could adapt your table to use: http://www.postgresql.org/docs/8.3/interactive/ltree.html

或者您可以自己使用 PL/PGSQL 函数遍历树.

Or you can traverse the tree yourself with a PL/PGSQL function.

像这样:

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

这篇关于是否可以进行递归 SQL 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！