是否可以进行递归SQL查询? [英] Is it possible to make a recursive SQL query?
问题描述
我有一个与此类似的表:
I have a table similar to this:
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组件.它具有处理遍历树的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
或者您可以使用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屋!