在多表层次结构中对列求和

Question

几个小时前，我发了一个关于两个表之间的层次结构的问题。这里是链接：https://dev59.com/-4nda4cB1Zd3GeqPGv5X

由于我的原始问题得到了正确和快速的答复，我为我的下一个问题创建了一个新帖子。然而，我被要求不要依赖于另一个问题，所以这里是情景描述：

我有以下几个表格：

    create table areas
(
  id            NUMBER not null,
  name          VARCHAR2(200) not null,
  parent_id     NUMBER
);

-- Top Level
Insert into areas (id, name)
 Values (1, 'Europe');
Insert into areas (id, name)
 Values (2, 'Americas');
Insert into areas (id, name)
 Values (3, 'Asia ex Japan');
Insert into areas (id, name)
 Values (4, 'Japan');

 -- Jurisdictions
Insert into areas (id, name, parent_id)
 Values (5, 'UK', 1);
Insert into areas (id, name, parent_id)
 Values (7, 'France', 1);
Insert into areas (id, name, parent_id)
 Values (6, 'Germany', 1);
Insert into areas (id, name, parent_id)
 Values (8, 'Italy', 1);
Insert into areas (id, name, parent_id)
 Values (9, 'US', 2);
Insert into areas (id, name, parent_id)
 Values (10, 'Australia', 3);
Insert into areas (id, name, parent_id)
 Values (11, 'New Zealand', 3);

create table places
(
  id            NUMBER not null,
  name          VARCHAR2(200) not null,
  area_id       NUMBER,
  parent_id     NUMBER,
  no_of_pubs    NUMBER
);

Insert into places (id, name, area_id, parent_id)
 Values (1, 'London', 5, NULL, 50);
Insert into places (id, name, area_id, parent_id)
 Values (2, 'Bath', 5, NULL, 20);
Insert into places (id, name, area_id, parent_id)
 Values (3, 'Liverpool', 5, NULL, 32);
Insert into places (id, name, area_id, parent_id)
 Values (4, 'Paris', 7, NULL, 64);
Insert into places (id, name, area_id, parent_id)
 Values (5, 'New York', 9, NULL, 76);
Insert into places (id, name, area_id, parent_id)
 Values (6, 'Chicago', 9, NULL, 5);
Insert into places (id, name, area_id, parent_id)
 Values (7, 'Kings Cross', 5, 1, 6);
Insert into places (id, name, area_id, parent_id)
 Values (8, 'Tower of London', 5, 1, 0);

以下代码表示数据：

with src as (
  select 'A' type, a.id, a.name, a.parent_id, null area_id from areas a
  union all
  select 'P', -p.id id, p.name, -p.parent_id parent_id, area_id from places p)
select 
  src.*, level
from 
  src
start with 
  type = 'A' and parent_id is null
connect by 
  parent_id = prior id or 
  parent_id is null and area_id = prior id

自从上次发布之后，我修改了位置表，使得每个“位置”现在都有一个“酒吧数量”列，那么在层次结构中计算这个最好的方法是什么？

例如，英国将显示108家酒吧，伦敦将显示56家酒吧，国王十字会显示6家酒吧。

希望这解释了我试图实现的内容。

我的当前想法是将“类型”和“ID”传递给一个函数，然后通过循环子记录并加总酒吧来计算结果。

在我开始之前，是否有更好的方法或者我正在正确的轨道上？

编辑：自从我发帖以来，我实际上已经让这个函数工作了，但如果只使用SQL就可以实现的话，我很有兴趣看到它。如果不行，我将发布我的解决方案，但我必须重新调整它，因为我的数据与我的示例不同。

再次感谢。

- ls_dev

---

请将问题独立成立，这意味着您不应该期望其他人阅读另一篇文章来理解这个问题。请编辑您的问题，删除与其他问题的相关引用，并提供回答此问题所需的所有信息。谢谢。 - Bob Jarvis - Слава Україні

谢谢Bob，我已经编辑了我的帖子。 - ls_dev

如果我们修改places表格的结构，为什么不直接更新示例表格和插入语句，让我们清楚你想要的是什么？同时也请提供你期望看到的结果输出。为什么要让我们去猜测呢？ - Boneist

添加了更多信息 - 我是新手，欢迎对我发布方式提供反馈！ - ls_dev

Answer 1

您可以像Sander在第一个问题的解决方案中那样做类似的事情；实际上，使用相同的CTE，再加上一个no_of_pubs列（尽管它获得的一些值目前未被使用）：

with src as (
  select 'A' type, a.id, a.name, a.parent_id, null area_id, 0 no_of_pubs
  from areas a
  union all
  select 'P', -p.id id, p.name, -p.parent_id parent_id, area_id, no_of_pubs
  from places p
)
select s1.name, s1.no_of_pubs, (
    select sum(s2.no_of_pubs)
    from src s2
    start with s2.id = s1.id
    connect by s2.parent_id = prior s2.id
    or (s2.parent_id is null and s2.area_id = prior s2.id)
  ) as rollup_no_of_pubs
from src s1;

That gives:

NAME                           NO_OF_PUBS ROLLUP_NO_OF_PUBS
------------------------------ ---------- -----------------
Europe                                  0               172
Americas                                0                81
Asia ex Japan                           0                 0
Japan                                   0                 0
UK                                      0               108
France                                  0                64
Germany                                 0                 0
Italy                                   0                 0
US                                      0                81
Australia                               0                 0
New Zealand                             0                 0
London                                 50                56
Bath                                   20                20
Liverpool                              32                32
Paris                                  64                64
New York                               76                76
Chicago                                 5                 5
Kings Cross                             6                 6
Tower of London                         0                 0

您想要的是与伦敦和英国价值相关的国王十字区，

SQL Fiddle演示。

- Alex Poole

---

太好了，谢谢！我完全忘记了Rollup.. 我应该记得那个惊人的宠物店例子。 - ls_dev