sql - 多表层次结构中的总和列

Question

几个小时前我问 this question about a hierarchy over two tables.

我为我的下一个问题发了一个新帖子，因为我的原始问题得到了正确而快速的回答。但是，我被要求不要依赖另一个问题，所以这是场景:

我有以下表格:

    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

自从上一篇文章以来，我已经更改了地点表，以便每个“地点”现在都有一个“number_of_pubs”列，在层次结构中计算它的最佳方法是什么？

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

希望这可以解释我正在努力实现的目标。

我目前的想法是将 'type' 和 'id' 传递给一个函数，然后该函数通过循环遍历子记录并将 pubs 相加来计算结果。

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

编辑:自从我发布了这个，我实际上已经让这个函数工作了，但我有兴趣看看是否可以只使用 SQL。如果不是，我将发布我的解决方案，但我将不得不重新编写它，因为我的数据与我的示例不同。

再次感谢。

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;

这给出了:

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

哪个有你想要的国王十字、伦敦和英国值(value)观

SQL Fiddle demo .