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

本文介绍了分层查询-计数属于父级和子级位置的记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有两个表PLACE和ADRESS.PLACE包括诸如亲子等级制度之类的地方.ADRESS包含具有PLACE_ID列的地址.这些表如下所示；

I have two tables PLACE and ADRESS. PLACE includes places like parent-child hierarcy. ADRESS includes adresses with PLACE_ID column. These tables as shown below;

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

我想找出那个地方有多少成员.我无法将子位置的成员数添加到父位置.我的查询如下所示；

I want to find out how many members are in which place. I couldn't add child places's member count to parent place. My query as shown below;

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> 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(请参阅评论):

Then, a few CTEs (see comments):

 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>

这篇关于分层查询-计数属于父级和子级位置的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Adress