sql—oracle中列的层次总和

swvgeqrz 于 2021-07-29 发布在 Java

关注(0)|答案(3)|浏览(303)

我有一个问题如下：

ID NAME  AMOUNT PARENTID  
1  Adam  1000   0  
2  John  2000   1  
3  Clark 1500   2  
4  Rita  1200   3  
5  jack  1600   3  
6  mark  1800   2  
7  Finn  1500   6  
8  Ryan  1100   6

因此，上面的数据是具有多个连接的查询的结果，它是一种层次结构或树，类似于：

1  
       |  
       2  
     /  \   
   3      6  
  / \     / \  
5   4    7   8

现在我需要修改我的查询以便得到以下结果

ID NAME  AMOUNT PARENTID  DownstreamSum   
1  Adam  1000   0         10700     
2  John  2000   1         8700      
3  Clark 1500   2         2800      
4  Rita  1200   3         0         
5  jack  1600   3         0         
6  mark  1800   2         2600      
7  Finn  1500   6         0          
8  Ryan  1100   6         0

因此，逻辑是父节点应该拥有子节点中所有下游子节点的总和 DownstreamSum 列。
例如：
id 6应该是id 7和id 8的总和
id 3应该是id 4和id 5的总和
但是id 2应该有id 3的数量和id 4和5的数量之和
我尝试了很多可能性 partition by 以及 group by 但是我没能得到想要的结果。

sql oracle recursive-query hierarchical-data

来源：https://stackoverflow.com/questions/63103369/hierarchical-sum-of-the-columns-in-oracle

3条答案

按热度按时间

wnavrhmk1#

这是查询，请核对。
sql小提琴链接

SELECT  Y.*, X.TOTAL_AMOUNT FROM (
    WITH hierarchy (PARENTID, ID) AS (SELECT PARENTID, ID
        FROM Hierarchy_result
        UNION ALL
        SELECT h.PARENTID, bs.ID
        FROM Hierarchy_result  bs, hierarchy  h WHERE bs.PARENTID=h.ID)
        SELECT h.PARENTID AS PARENTID,
        --SUM(FR.AMOUNT)
        /*SUM((CASE WHEN FR.ID=h.PARENTID
            THEN FR.amount
            ELSE 0.0 END))*/
        SUM(FR.amount) TOTAL_AMOUNT
    FROM hierarchy h, Hierarchy_result FR WHERE FR.ID=h.ID
    GROUP BY h.PARENTID 
    ORDER BY h.PARENTID) X, Hierarchy_result Y WHERE X.PARENTID(+) = Y.ID ORDER BY Y.ID;

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

62o28rlo2#

典型的递归cte（从oracle11gr2开始提供）将允许您将分支遍历到叶子。然后是一个简单的 SUM() 给你想要的结果。例如：

with
n (root_id, current_id, name, amount, parentid) as (
  select id, id, name, amount, parentid from t
 union all
  select n.root_id, t.id, t.name, t.amount, t.parentid
  from n
  join t on t.parentid = n.current_id
)
select t.id, t.name, t.amount, t.parentid,
  sum(n.amount) - t.amount as downstreamsum 
from t
join n on n.root_id = t.id
group by t.id, t.name, t.amount, t.parentid
order by t.id

结果：

ID  NAME   AMOUNT  PARENTID  DOWNSTREAMSUM 
--- ------ ------- --------- ------------- 
 1  Adam     1000         0          10700 
 2  John     2000         1           8700 
 3  Clark    1500         2           2800 
 4  Rita     1200         3              0 
 5  jack     1600         3              0 
 6  mark     1800         2           2600 
 7  Finn     1500         6              0 
 8  Ryan     1100         6              0

参见db<>fiddle上的运行示例。

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

aelbi1ox3#

此查询还计算本机和子机的总和和计数：

with mcnt (
    id, parentid, name, amount
    ,rnk
    ,level_members_cnt
    ,level_members_sum
    ,own_members_cnt
    ,own_members_sum
    ,child_members_cnt
    ,child_members_sum
    ) as (
   select 
          p.id, p.parentid, p.name, p.amount
        , row_number()over(partition by p.parentid order by p.id) rnk
        , sum((select count(*) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) as level_members_cnt
        , sum((select sum(m.amount) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) as level_members_sum
        , (select count(*) from Hierarchy_result m where m.parentid=p.id) as own_members_cnt
        , (select nvl(sum(m.amount),0) from Hierarchy_result m where m.parentid=p.id) as own_members_sum
        , 0 as child_members_cnt
        , 0 as child_members_sum
   from Hierarchy_result p
   where not exists(select null from Hierarchy_result child where p.id = child.parentid)
   union all
   select p.id, p.parentid, p.name, p.amount
        , row_number()over(partition by p.parentid order by p.id) rnk
        , sum((select count(*) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) level_members_cnt
        , sum((select sum(m.amount) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) level_members_sum
        , (select count(*) from Hierarchy_result m where m.parentid=p.id) as own_members_cnt
        , (select nvl(sum(m.amount),0) from Hierarchy_result m where m.parentid=p.id) as own_members_sum
        , mcnt.level_members_cnt + mcnt.child_members_cnt as child_members_cnt
        , nvl(mcnt.level_members_sum,0) + nvl(mcnt.child_members_sum,0) as child_members_sum
   from mcnt, Hierarchy_result p
   where mcnt.parentid=p.id
     and mcnt.rnk=1
)
select
       id, parentid, name, amount, child_members_cnt, own_members_cnt
      ,child_members_cnt+own_members_cnt as total_cnt
      ,child_members_sum,own_members_sum
      ,child_members_sum+own_members_sum as total_sum
from mcnt
order by id;

完整示例和示例数据：

with Hierarchy_result ("ID", "NAME", "AMOUNT", "PARENTID") as (
    select 1, 'Adam', 1000, 0 from dual union all
    select 2, 'John', 2000, 1 from dual union all
    select 3, 'Clark', 1500, 2 from dual union all
    select 4, 'Rita', 1200, 3 from dual union all
    select 5, 'jack', 1600, 3 from dual union all
    select 6, 'mark', 1800, 2 from dual union all
    select 7, 'Finn', 1500, 6 from dual union all
    select 8, 'Ryan', 1100, 6 from dual
)
,mcnt (
    id, parentid, name, amount
    ,rnk
    ,level_members_cnt
    ,level_members_sum
    ,own_members_cnt
    ,own_members_sum
    ,child_members_cnt
    ,child_members_sum
    ) as (
   select 
          p.id, p.parentid, p.name, p.amount
        , row_number()over(partition by p.parentid order by p.id) rnk
        , sum((select count(*) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) as level_members_cnt
        , sum((select sum(m.amount) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) as level_members_sum
        , (select count(*) from Hierarchy_result m where m.parentid=p.id) as own_members_cnt
        , (select nvl(sum(m.amount),0) from Hierarchy_result m where m.parentid=p.id) as own_members_sum
        , 0 as child_members_cnt
        , 0 as child_members_sum
   from Hierarchy_result p
   where not exists(select null from Hierarchy_result child where p.id = child.parentid)
   union all
   select p.id, p.parentid, p.name, p.amount
        , row_number()over(partition by p.parentid order by p.id) rnk
        , sum((select count(*) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) level_members_cnt
        , sum((select sum(m.amount) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) level_members_sum
        , (select count(*) from Hierarchy_result m where m.parentid=p.id) as own_members_cnt
        , (select nvl(sum(m.amount),0) from Hierarchy_result m where m.parentid=p.id) as own_members_sum
        , mcnt.level_members_cnt + mcnt.child_members_cnt as child_members_cnt
        , nvl(mcnt.level_members_sum,0) + nvl(mcnt.child_members_sum,0) as child_members_sum
   from mcnt, Hierarchy_result p
   where mcnt.parentid=p.id
     and mcnt.rnk=1
)
select
       id, parentid, name, amount, child_members_cnt, own_members_cnt
      ,child_members_cnt+own_members_cnt as total_cnt
      ,child_members_sum,own_members_sum
      ,child_members_sum+own_members_sum as total_sum
from mcnt
order by id;

结果

ID PARENTID NAME  CHILD_MEMBERS_SUM OWN_MEMBERS_SUM  TOTAL_SUM CHILD_MEMBERS_CNT OWN_MEMBERS_CNT  TOTAL_CNT
--- -------- ----- ----------------- --------------- ---------- ----------------- --------------- ----------
  1        0 Adam               8700            2000      10700                 6               1          7
  2        1 John               5400            3300       8700                 4               2          6
  3        2 Clark                 0            2800       2800                 0               2          2
  4        3 Rita                  0               0          0                 0               0          0
  5        3 jack                  0               0          0                 0               0          0
  6        2 mark                  0            2600       2600                 0               2          2
  7        6 Finn                  0               0          0                 0               0          0
  8        6 Ryan                  0               0          0                 0               0          0

基于我的另一个解决方案：属于父位置和子位置的分层查询计数记录

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

我来回答

sql—oracle中列的层次总和

3条答案

相关问题

热门标签

最新问答