首页 文章

在Postgres中使用sum进行递归查询

提问于
浏览 次
3

我必须在我的数据库中存储很多项目 . 每个项目都可以有子项目 . 结构看起来像一棵树：

Project
                            /           |             \
                    ProjectChild1    ProjectChild2    [...]    ProjectChild[n]
                    /       |
 ProjectChildOfChild1    ProjectChildOfChild2

树的层次是未知的 . 我正在考虑创建一个这样的表：

表 Projects ：

project_ID id_unique PRIMARY_KEY
project_NAME text
project_VALUE numeric
project_PARENT id_unique

在这种情况下，列 project_PARENT 将存储父项目的id（如果存在） .

对于我的应用程序，我需要检索项目的总值，为此我需要总结每个项目子项和根项目的值 .

我知道我需要使用递归，但我不知道如何在Postgres中这样做 .

sql postgresql recursion common-table-expression

2 回答

  • 5

    这是@a_horse's correct answer的简化版本（在评论中与OP讨论后） .
    适用于递归中任何（合理有限的）级别 .

    给定project_id的总价格

    WITH RECURSIVE cte AS (
   SELECT project_id AS project_parent, project_value
   FROM   projects
   WHERE  project_id = 1 -- enter id of the base project here !

   UNION  ALL 
   SELECT p.project_id, p.project_value
   FROM   cte
   JOIN   projects p USING (project_parent)
)
SELECT sum(project_value) AS total_value
FROM   cte;

    要一次性获得所有项目的总成本：

    同时为所有项目

    WITH RECURSIVE cte AS (
   SELECT project_id, project_id AS project_parent, project_value
   FROM   projects
   WHERE  project_parent IS NULL  -- all base projects

   UNION  ALL 
   SELECT c.project_id, p.project_id, p.project_value
   FROM   cte c
   JOIN   projects p USING (project_parent)
)
SELECT project_id, sum(project_value) AS total_value
FROM   cte
GROUP  BY 1
ORDER  BY 1;

    SQL Fiddle（带有正确的测试用例） .

    回复于
  • 3

    像这样的东西：

    with recursive project_tree as (
   select project_id, 
          project_name,
          project_value,
          project_parent
   from projects
   where project_id = 42 -- << the id of the "base" project
   union all 
   select p.project_id,
          p.project_name,
          p.project_value,
          p.project_parent
   from projects p
     join project_tree t on t.project_id = p.project_parent
)
select sum(project_value)
from project_tree;

    联合的第一部分需要选择要评估的项目（“子”项目） . 递归连接将向上遍历树并检索所有父项目 .

    回复于

相关问题