属于父位置和子位置的sql分层查询计数记录

puruo6ea 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(383)

我有两张table，位置和地址。地方包括像亲子层次的地方。地址包括带有place\u id列的地址。这些表格如下所示；

PLACE

ID      PARENT_ID       NAME        CONTINENT   
11      null            USA         America
22      11              New York    America
33      22              Manhattan   America
44      null            Brasil      America
55      44              Rio         America
66      null            France      Europe
77      66              Paris       Europe  
88      66              Nice        Europe

MEMBER  

ID      PLACE_ID    NAME    ADRESS
1       22          ..      ..
2       77          ..      ..
3       33          ..      ..
4       22          ..      ..
5       55          ..      ..
6       55          ..      ..
7       88          ..      ..
8       88          ..      ..
9       88          ..      ..
10      22          ..      ..

Expected Result:

ID      PARENT_ID       MEMBER_COUNT    PLACE_NAME      CONTITNET       
11      null                4           USA             America
22      11                  4           New York        America
33      22                  1           Manhattan       America
44      null                2           Brasil          America
55      44                  2           Rio             America
66      null                4           France          Europe
77      66                  1           Paris           Europe  
88      66                  3           Nice            Europe

我想知道在哪个地方有多少会员。我无法将子位置的成员计数添加到父位置。我的查询如下所示；

WITH MEMBER_COUNT_BY_PLACE AS (
    SELECT  P.PLACE_ID, COUNT(P.ID) AS MEMBER_COUNT
    FROM MEMBER P
    GROUP BY P.PLACE_ID
) SELECT C.ID,  C.NAME, C.PARENT_ID AS PARENTID, C.CONTINENT, SUM(NVL(D.MEMBER_COUNT, 0)) AS MEMBER_COUNT
  FROM PLACE C LEFT JOIN MEMBER_COUNT_BY_PLACE D ON D.PLACE_ID = C.ID
  START WITH D.PLACE_ID IS NOT NULL
  CONNECT BY PRIOR C.PARENT_ID = C.ID
  GROUP BY C.ID, C.NAME, C.PARENT_ID, C.CONTINENT
  ORDER BY CONTINENT ASC, PARENT_ID ASC NULLS FIRST;

谢谢你的帮助。

sql oracle hierarchical-data

来源：https://stackoverflow.com/questions/62851761/hierarchical-query-counting-records-that-belongs-to-parent-and-child-places

2条答案

按热度按时间

jfgube3f1#

这里有一个选择：
首先是样本数据：

SQL> with
  2  -- sample data
  3  place (id, parent_id, name, continent) as
  4    (select 11, null, 'USA'      , 'America' from dual union all
  5     select 22, 11  , 'New York' , 'America' from dual union all
  6     select 33, 22  , 'Manhattan', 'America' from dual union all
  7     select 44, null, 'Brasil'   , 'America' from dual union all
  8     select 55, 44  , 'Rio'      , 'America' from dual union all
  9     select 66, null, 'France'   , 'Europe'  from dual union all
 10     select 77, 66  , 'Paris'    , 'Europe'  from dual union all
 11     select 88, 66  , 'Nice'     , 'Europe'  from dual
 12    ),
 13  member (id, place_id) as
 14    (select  1, 22 from dual union all
 15     select  2, 77 from dual union all
 16     select  3, 33 from dual union all
 17     select  4, 22 from dual union all
 18     select  5, 55 from dual union all
 19     select  6, 55 from dual union all
 20     select  7, 88 from dual union all
 21     select  8, 88 from dual union all
 22     select  9, 88 from dual union all
 23     select 10, 22 from dual
 24    ),

然后，一些CTE（见注解）：

25  -- naively, count members of leaf nodes
 26  naive as
 27    (select m.place_id, count(*) cnt
 28     from member m
 29     group by m.place_id
 30    ),
 31  -- set root parent node to each row
 32  cbr as
 33    (select connect_by_root p.id as tpid,
 34       p.id, p.parent_id, n.cnt
 35     from place p left join naive n on p.id = n.place_id
 36     connect by prior p.id = p.parent_id
 37     start with p.parent_id is null
 38     order by p.id
 39    ),
 40  -- how many members does each root node have?
 41  sumtpid as
 42    (select c.tpid, sum(c.cnt) cnt
 43     from cbr c
 44     group by c.tpid
 45    )
 46  -- join CBR + SUMTPID + PLACE for the final result
 47  select c.id, c.parent_id, nvl(c.cnt, s.cnt) member_count,
 48    p.name place_name,
 49    p.continent
 50  from cbr c join sumtpid s on s.tpid = c.tpid
 51             join place p on p.id = c.id
 52  order by c.id;

结果是：

ID  PARENT_ID MEMBER_COUNT PLACE_NAM CONTINE
---------- ---------- ------------ --------- -------
        11                       4 USA       America
        22         11            3 New York  America
        33         22            1 Manhattan America
        44                       2 Brasil    America
        55         44            2 Rio       America
        66                       4 France    Europe
        77         66            1 Paris     Europe
        88         66            3 Nice      Europe

8 rows selected.

SQL>

赞(0）回复(0）举报 2021-07-24

guicsvcw2#

此查询还聚合子成员：

with mcnt (id, parent_id, name, continent, child_members, own_members, ff,level_members) as (
   select 
          p.id, p.parent_id, p.name, p.continent
        , 0 as child_members
        , (select count(*) from member m where m.place_id=p.id) as own_members
        , row_number()over(partition by p.parent_id order by p.id) ff
        , sum((select count(*) from member m where m.place_id=p.id))over(partition by p.parent_id) level_members
   from place p
   where not exists(select null from place child where p.id = child.parent_id)
   union all
   select p.id, p.parent_id, p.name, p.continent
        , mcnt.level_members + mcnt.child_members as child_members
        , (select count(*) from member m where m.place_id=p.id) as own_members
        , row_number()over(partition by p.parent_id order by p.id) ff
        , sum((select count(*) from member m where m.place_id=p.id))over(partition by p.parent_id) level_members
   from mcnt, place p
   where mcnt.parent_id=p.id
     and mcnt.ff=1
)
select
       id, parent_id, name, continent, child_members, own_members
      ,child_members+own_members as total_cnt
from mcnt
order by id;

完整示例：

with
-- sample data
place (id, parent_id, name, continent) as
  (select 11, null, 'USA'      , 'America' from dual union all
   select 22, 11  , 'New York' , 'America' from dual union all
   select 33, 22  , 'Manhattan', 'America' from dual union all
   --
  select 35, 33  , 'Central Park', 'America' from dual union all
   select 44, null, 'Brasil'   , 'America' from dual union all
   select 55, 44  , 'Rio'      , 'America' from dual union all
   select 66, null, 'France'   , 'Europe'  from dual union all
   select 77, 66  , 'Paris'    , 'Europe'  from dual union all
   select 88, 66  , 'Nice'     , 'Europe'  from dual
  ),
member (id, place_id) as
  (select  1, 22 from dual union all
   select  2, 77 from dual union all
   select  3, 33 from dual union all
   select  4, 22 from dual union all
   select  5, 55 from dual union all
   select  6, 55 from dual union all
   select  7, 88 from dual union all
   select  8, 88 from dual union all
   select  9, 88 from dual union all
   select 10, 22 from dual
   --
   union all select 1001, 35 from dual
   union all select 1002, 35 from dual
   union all select 1003, 35 from dual
  ),
mcnt (id, parent_id, name, continent, child_members, own_members, ff,level_members) as (
   select 
      p.id, p.parent_id, p.name, p.continent
    , 0 as child_members
    , (select count(*) from member m where m.place_id=p.id) as own_members
    , row_number()over(partition by p.parent_id order by p.id) ff
    , sum((select count(*) from member m where m.place_id=p.id))over(partition by p.parent_id) level_members
   from place p
   where not exists(select null from place child where p.id = child.parent_id)
   union all
   select p.id, p.parent_id, p.name, p.continent
    , mcnt.level_members + mcnt.child_members as child_members
    , (select count(*) from member m where m.place_id=p.id) as own_members
    , row_number()over(partition by p.parent_id order by p.id) ff
    , sum((select count(*) from member m where m.place_id=p.id))over(partition by p.parent_id) level_members
   from mcnt, place p
   where mcnt.parent_id=p.id
 and mcnt.ff=1
)
select
   id, parent_id, name, continent, child_members, own_members
  ,child_members+own_members as total_cnt
from mcnt
order by id;

结果：

ID  PARENT_ID NAME         CONTINE CHILD_MEMBERS OWN_MEMBERS  TOTAL_CNT
---------- ---------- ------------ ------- ------------- ----------- ----------
        11            USA          America             7           0          7
        22         11 New York     America             4           3          7
        33         22 Manhattan    America             3           1          4
        35         33 Central Park America             0           3          3
        44            Brasil       America             2           0          2
        55         44 Rio          America             0           2          2
        66            France       Europe              4           0          4
        77         66 Paris        Europe              0           1          1
        88         66 Nice         Europe              0           3          3

9 rows selected.

赞(0）回复(0）举报 2021-07-24

我来回答

属于父位置和子位置的sql分层查询计数记录

2条答案

相关问题

热门标签

最新问答