可以制作递归SQL查询吗？

Question

我有一张类似于这样的桌子：

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

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

现在有一点我无法解决。给定一个parentid，是否可能编写一个SQL语句来累加该parentid下所有value字段的值，并递归遍历树的分支？

更新：我正在使用posgreSQL，所以无法使用高级的MS-SQL功能。无论如何，我希望这个问题被视为一个通用的SQL问题。

- Adam Pierce

---

我正在使用posgreSQL，因此无法使用花哨的MS-SQL功能。无论如何，我希望将其视为一般的SQL问题。顺便说一句，我对在提问后15分钟内获得6个答案印象深刻！加油，堆栈溢出！ - Adam Pierce

这是分层数据。我发现Anthony Mollinaro在《SQL Cookbook（奥莱利）》中对于分层数据的讨论很实用，他覆盖了几乎所有流行的DBMS，包括PostrgreSQL。 - J. Polfer

如果您是从谷歌搜索而来，请查看@Chris KL的回复，因为PostgreSQL 8.4及以上版本支持递归查询。 - regilero

Answer 1

下面是一个使用公共表达式的示例脚本:

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 语句结果定义的。

第一个 select 获取根节点（where id = :selectedid）。

第二个 select 迭代地跟随前面结果的子级，直到没有返回值为止。

最终结果可以像正常表格一样处理。在这种情况下，对 val 列进行求和。

- Endy Tjahjono

---

如果我正确理解你的代码，它会获取一些与selectedid匹配的(id, value)元组。然后将这些元组与一个新的sumthis实例连接起来，该实例再次包含相同的元组。那么为什么这个查询会终止呢？我的理解失败可能在于如何设置:selectedid，或者子sumthis表的值来自哪里。 - lucidbrot

Answer 2

自8.4版本以来，PostgreSQL使用SQL标准的WITH语法为通用表达式提供递归查询支持。

- Chris KL

Answer 3

如果你想要一个可移植的解决方案，在任何 ANSI SQL-92 RDBMS 上都能使用，你需要在表中添加一列。

Joe Celko 是 SQL 中存储层次结构的 嵌套集 方法的原始作者。你可以通过 Google "nested sets" hierarchy 了解更多背景信息。

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

这是我对 Nested Sets 的总结尝试，但由于我不是 Joe Celko，所以会显得非常简略：SQL 是基于集合的语言，邻接模型（存储父 ID）不是层次结构的基于集合的表示。因此，没有纯粹的基于集合的方法来查询邻接架构。

然而，最近几年，大多数主要平台已经引入了扩展来处理这个问题。因此，如果有人回复了一个特定于 Postgres 的解决方案，请务必使用它。

- Portman

---

@Portman 我看过了嵌套集。似乎是个不错的想法，但插入/删除成本似乎非常高。 - Kibbee

是的，看起来是这样。但请相信我——一旦您编写了CRUD（增删改查）过程，它的性能非常出色。 - Portman

嵌套集模型中的每次更新都需要更新表中超过50％的行。这是一个巧妙的机制，但在任何实际情况下都完全不适用。我通常是Celko的粉丝，但他在这一点上是错误的。 - Sarah G

智能企业链接已失效。 - bf2020

Answer 4

在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

• 或者您可以使用PL/PGSQL函数自己遍历树形结构。

类似于这样：

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

- Please delete this account

Answer 5

在SQL中进行递归查询的标准方法是使用递归CTE。PostgreSQL从8.4版本开始支持它们。

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

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

- Quassnoi

---

现在所有主要的DBMS都支持递归CTE，但MySQL和SQLite除外。+1 - user330315

也许我做错了什么，但如果在第一个SELECT子句中指定了超过一个字段，例如SELECT id, name，那么在SELECT fn_hierarchy行会出现“每个UNION查询必须具有相同数量的列”错误。 我猜在最后的SELECT中，我可以重新加入到example表以获取其余字段，但这不再那么优雅了。 - poshest

Answer 6

如果您使用的是SQL Server 2005，有一种非常酷的方法可以使用公共表达式来完成。它消除了创建临时表的所有繁琐工作，并基本上只需要使用WITH和UNION即可完成所有操作。这里有一个好的教程：

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

- Jonathan Holland

Answer 7

使用公共表达式。

可能需要指出这仅适用于SQL Server 2005或更高版本。 Dale Ragan

这里有一篇文章介绍了SqlTeam的递归方法，但没有使用公共表达式。

- Darren Kopp

---

可能需要指出这仅适用于 SQL Server 2005 或更高版本。 - Dale Ragan

现在不想查了，但我知道 Oracle 10g 有 With 子句，不知道这个也能用吗。 - George Mauer

Oracle 11gR2引入了支持递归WITH子句，之前WITH子句不能引用自身。 在以前的版本中，Oracle从至少7或8版开始就有了自己的分层查询语法（START WITH+CONNECT BY），可能更早。 - Jeffrey Kemp

Answer 8

以下代码已经编译并且测试通过。

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

在我的情况下，需要使用"child <> parent"条件，因为节点指向自身。

- Richard Gomes

Answer 9

Oracle有“START WITH”和“CONNECT BY”

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

- Jason

Answer 10

没有一个例子适合我，所以我像这样修复了它：

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;

- Slawa