我有一张与此类似的表：
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) |
查看本文：
如果您使用的是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; |
尽管问题已得到很好的回答，但作为简短说明，应该注意的是，如果我们将其视为：
generic SQL question
然后，SQL实现相当简单，因为SQL'99允许通过WITH RECURSIVE语句在规范中进行线性递归(尽管我相信没有RDBMS会完全实现该标准)。因此，从理论上讲，我们现在可以执行此操作。
Oracle具有" START WITH"和" CONNECT BY"
1
2
3
4
5
6
7
8
| 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
我认为使用HierarchyID
在SQL 2008中更容易
这是SQL Server吗？您是否可以编写一个TSQL存储过程来循环并将结果合并在一起？
我也很感兴趣是否有一种仅使用SQL的方式来执行此操作。从我记得的地理数据库类的资料来看，应该有。
如果您需要存储任意图形，而不仅仅是层次结构，则可以将Postgres推到一边，然后尝试使用诸如AllegroGraph：
的图形数据库
图形数据库中的所有内容都存储为三元组(源节点，边，目标节点)，它为您提供了一流的支持，可操纵图形结构并使用类似SQL的语言对其进行查询。
它不能与Hibernate或Django ORM之类的东西很好地集成在一起，但是如果您对图结构很认真(不仅像Nested Set模型这样的层次结构会给您)，请检查一下它。
我也相信Oracle终于在其最新产品中增加了对真实图的支持，但是我很惊讶它花了这么长时间，该模型可能会带来很多问题。
