首页 / 知识

关于postgresql：是否可以进行递归SQL查询？

2023-04-14 18:30:00

Is it possible to make a recursive SQL query?

我有一张与此类似的表：

1
2
3
4
5

CREATE TABLE example (
id INTEGER PRIMARY KEY,
name CHAR(200),
parentid INTEGER,
VALUE INTEGER);

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

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

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

顺便说一句，我很高兴在提出问题的15分钟内能得到6个答案！去堆栈溢出！

以下是使用公用表表达式的示例脚本：

1
2
3
4
5
6
7
8
9
10

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列求和。

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

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

Joe Celko是嵌套集方法在SQL中存储层次结构的原始作者。您可以使用Google"嵌套集"层次结构来了解有关背景的更多信息。

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

这是我尝试对嵌套集进行总结，因为我不是Joe Celko，嵌套集将严重不足：SQL是基于集合的语言，而邻接模型(存储父ID)不是基于集合的表示形式层次结构。因此，没有用于查询邻接模式的纯基于集合的方法。

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

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

如果可以安装模块，请查看tablefunc组件。它具有一个处理遍历树的connectby()函数。 http://www.postgresql.org/docs/8.3/interactive/tablefunc.html
还要检查ltree贡献，您可以修改表以使用：http://www.postgresql.org/docs/8.3/interactive/ltree.html
或者您可以使用PL / PGSQL函数自己遍历树。

类似这样的内容：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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中进行递归查询的标准方法是递归CTE。从8.4开始，PostgreSQL支持它们。

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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中的分层查询

如果您使用的是SQL Server 2005，则可以使用"公用表表达式"来实现此目的。

从创建临时表中消除了所有繁琐的工作，并且基本上允许您仅使用WITH和UNION来完成所有工作。

这是一个很好的教程：

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

使用公用表表达式。

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

这是有关SqlTeam没有通用表表达式的递归的文章。

下面的代码可以编译，并且已经过测试。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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

在我的情况下，条件"子代<>父代"是必需的，因为节点指向自己。

玩得开心：)

这些示例都无法为我工作，因此我已将其修复为：

1
2
3
4
5
6
7
8
9
10
11
12
13

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;

尽管问题已得到很好的回答，但作为简短说明，应该注意的是，如果我们将其视为：