是否可以进行递归 SQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/53108/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to make a recursive SQL query?
提问by Adam Pierce
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 个答案！去堆栈溢出！
采纳答案by Portman
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
Also check out the ltree contrib, which you could adapt your table to use: http://www.postgresql.org/docs/8.3/interactive/ltree.html
Or you can traverse the tree yourself with a PL/PGSQL function.
如果您可以安装模块，请查看 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 函数遍历树。
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 = ;
if found then
return next results;
for child in select id from example
where parent_id =
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);
回答by Endy Tjahjono
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 sumthisthat has columns idand val. It is defined as the result of two selects merged with union all.
上面的脚本创建了一个名为的“虚拟”表sumthis，其中包含列id和val. 它被定义为两个选择合并的结果union all。
First selectgets the root (where id = :selectedid).
首先select得到根 ( where id = :selectedid)。
Second selectfollows the children of the previous results iteratively until there is nothing to return.
Secondselect迭代地跟踪先前结果的子项，直到没有任何返回。
The end result can then be processed like a normal table. In this case the val column is summed.
然后可以像处理普通表一样处理最终结果。在这种情况下，将 val 列相加。
回答by Chris KL
Since version 8.4, PostgreSQL has recursive query supportfor common table expressions using the SQL standard WITHsyntax.
从 8.4 版开始，PostgreSQL使用 SQL 标准语法对公共表表达式提供递归查询支持WITH。
回答by Portman
If you want a portable solution that will work on any ANSI SQL-92RDBMS, you will need to add a new column to your table.
如果您想要一个适用于任何 ANSI SQL-92RDBMS的可移植解决方案，您将需要向您的表中添加一个新列。
Joe Celko is the original author of the Nested Setsapproach to storing hierarchies in SQL. You can Google "nested sets" hierarchyto understand more about the background.
Joe Celko 是在 SQL 中存储层次结构的嵌套集方法的原作者。你可以谷歌“嵌套集”层次结构来了解更多关于背景的信息。
Or you can just rename parentid to leftidand add a rightid.
或者您可以将 parentid 重命名为leftid并添加一个rightid。
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 特定的解决方案，请务必使用它。
回答by Quassnoi
A standard way to make a recursive query in SQLare recursive CTE. PostgreSQLsupports them since 8.4.
进行递归查询的标准方法SQL是 recursive 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 =
UNION ALL
SELECT fn_hierarchy(id)
FROM example
WHERE parentid =
$$
LANGUAGE 'sql';
SELECT *
FROM fn_hierarchy(1)
See this article:
看这篇文章：
回答by FlySwat
If your using SQL Server 2005, there is a really cool way to do this using Common Table Expressions.
如果您使用 SQL Server 2005，那么使用 Common Table Expressions 是一种非常酷的方法。
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.
它消除了创建临时表的所有繁琐工作，并且基本上允许您仅使用 WITH 和 UNION 来完成所有工作。
Here is a good tutorial:
这是一个很好的教程：
http://searchwindevelopment.techtarget.com/tip/0,289483,sid8_gci1278207,00.html
http://searchwindevelopment.techtarget.com/tip/0,289483,sid8_gci1278207,00.html
回答by Darren Kopp
use a common table expression.
使用公用表表达式。
May want to indicate this is SQL Server 2005 or above only. Dale Ragan
可能要表明这是仅 SQL Server 2005 或更高版本。戴尔·拉根
here's an articleon recursion by SqlTeam without common table expressions.
回答by Richard Gomes
The following code compiles and it's tested 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 = ;
if found then
for entry in select child from example where parent = 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 :)
玩得开心 ：）
回答by Dr.Pil
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 RECURSIVEstatement. So from a theoretical perspective we can do this right now.
那么 SQL 实现是相当简单的，因为 SQL'99 允许通过WITH RECURSIVE语句在规范中进行线性递归（尽管我相信没有 RDBMS 完全实现标准）。所以从理论的角度来看，我们现在可以做到这一点。
回答by Slawa
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 = loop
return next results;
for recs in select * from project_subtree(results.id) loop
return next recs;
end loop;
end loop;
return;
end;
