合并SQL中的相关组织记录，其中组织之间存在父子关系

Question

我正在尝试构建一个用于Yellowfin BI报告的数据表。这样做的一个限制是不能创建临时表，然后将其删除到数据库中。我正在从一个我无法控制的现有数据库中提取数据。我只能使用SQL查询现有数据。

在源数据库中有两个表我需要使用。为了清楚起见，我简化了它们。第一个包含组织。它有一个ORG_ID列，其中包含每个组织的唯一ID，还有一个PARENT_ORG_ID列，指示哪个组织是列表中其他组织的母公司：

ORG_ID     PARENT_ORG_ID
1          Null
2          1
3          5
4          5
5          Null
6          1

使用上表，我可以看到组织之间存在以下关系：

ORG_ID     RELATED_ORGANISATIONS
1          2 and 6
2          1 and 6
3          5 and 4
4          5 and 3
5          4 and 3
6          1 and 2

我不确定在查询中表示这些连接的最佳方式，因为我需要将这些关系与第二个表一起使用。

我的第二张表是组织和欠款清单：

ORG_ID     MONEY_OWED
1          5
2          10
3          0
4          15
5          20
6          5

我需要实现的是一个表格，我可以搜索任何一个ORG_ID，并查看该组织和所有相关组织的组合数据。在我的例子中，这可能是一个类似这样的结果表：

ORG_ID     MONEY_OWED_BY_ALL_RELATED_ORGS
1          20
2          20
3          35
4          35
5          35
6          20

我想我应该用CTE来处理组织之间的关系，但我无法理解。

如有任何帮助，我们将不胜感激！

Answer 1

对于您的特定示例，您可以使用：

select o.*,
sum(mo.money_owed) over (partition by coalesce(o.parent_org_id, o.org_id)) as parent_owed
from organizations o left join
money_owed mo
on mo.org_id = o.org_id;

这之所以有效，是因为您的组织只有一个层次的深度——这与您的样本数据一致。