sql - Postgres 中带总和的递归查询

Question

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

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

树的级别未知。我想创建一个这样的表:

表项目:

project_ID id_unique PRIMARY_KEY
project_NAME text
project_VALUE numeric
project_PARENT id_unique

在这种情况下，project_PARENT 列将存储父项目的 ID(如果存在)。

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

我知道我需要使用递归，但我不知道如何在 Postgres 中执行此操作。

Answer 1

这是 @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;

db<> fiddle here (带有正确的测试用例)
<子>旧sqlfiddle