Postgres中总和的递归查询 [英] Recursive query with sum in Postgres

问题描述

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

I must store a lot of projects in my DB. Each project can have child projects. The structure looks like a tree:

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

树的级别未知。
我正在考虑创建一个像这样的表：

The level of the tree is unknow. I'm thinking to create a table like this:

表 Projects ：

project_ID id_unique PRIMARY_KEY
project_NAME text
project_VALUE numeric
project_PARENT id_unique

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

In this case, the column project_PARENT will store the id of the parent project, if exists.

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

For my application I need to retrieve the total value of a project, for this I need to sum the values of every project child and the root project.

我知道我需要使用递归性，但是我不知道如何在Postgres中做到这一点。

I know that I need to use recursivity, but I don't know how to do this in Postgres.

推荐答案

这是 @a_horse的正确答案（在与OP讨论后进行评论）。

在递归中使用 any （合理地有限）多个级别。

This is a simplified version of @a_horse's correct answer (after discussion with OP in comments).
Works with any (reasonably finite) number of levels in the recursion.

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;

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

To get total cost for all projects at once:

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小提琴 （带有正确的测试用例）。

SQL Fiddle (with a correct test case).

这篇关于Postgres中总和的递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！