查询显示每个人的所有层次结构中所有收入的总和

Question

我有一个Oracle数据库，其中包含表People和Earnings。每个人都有一个领导，许多人可以拥有同一个领导。领导者也可以拥有自己的领导者。所以有一个层次结构。下面是与我现在已经

http://sqlfiddle.com/#!4/15e85/3

我需要创建一个查询，将统计所有下属（对所有下级层级的）每个人的收入的总和查询的例子。所以主要领导者的总和将是除收入之外的所有收入的总和。如何实现这一目标？

Answer 1

SELECT p.root, sum(e.EARNINGVALUE) s 
FROM Earnings e JOIN 
(SELECT p.IdPerson, 
     connect_by_root p.IdPerson root, 
     connect_by_isleaf lf 
FROM People p 
CONNECT BY PRIOR p.IdPerson= p.IdLeader 
) p on p.IdPerson = e.IdPerson 
where root != p.IdPerson 
     -- uncomment this if you want to calculate earnings of people who don't have subordinates 
     -- or lf = 1 
GROUP BY root; 

的CONNECT_BY_ISLEAF虚列返回1，如果当前行是由CONNECT BY条件定义的树的叶 。否则它 返回0.此信息指示给定的行是否可以进一步展开以显示更多的层次结构。

CONNECT_BY_ROOT是一个一元运算符，仅在分层查询中有效。当您使用此运算符限定列时，Oracle使用来自根行的数据返回列值。这个 运算符扩展了分层查询的CONNECT BY [PRIOR]条件 的功能。

• 甲加入，如果存在的话，首先，进行评价，无论是在FROM子句或WHERE子句谓词被指定的连接：

  甲骨文如下处理层次查询。

• 的CONNECT BY条件进行评价。

• 任何剩余的WHERE子句谓词进行求值。

如果不指定与甲骨文启动开始，每行。所以每行都可以找到下属的收入。

这里是不CONNECT_BY_ROOT版本：

with tmp_tree as (
    SELECT p.IdPerson, rownum rw, level lvl 
    FROM People p 
    CONNECT BY PRIOR p.IdPerson = p.IdLeader 
), 
tmp_tree_with_root as (
    SELECT t1.IdPerson, t2.IdPerson root 
    FROM (SELECT IdPerson, min(rw) over(partition by root_group) root_rw 
      FROM (SELECT IdPerson, rw, 
         rw - sum(case when lvl > 1 then 1 else 0 end) 
          over(order by rw) root_group 
       FROM tmp_tree) 
     ) t1 
     join tmp_tree t2 
     on t1.root_rw = t2.rw 
) 
SELECT p.root, sum(e.EARNINGVALUE) s 
FROM Earnings e JOIN tmp_tree_with_root p 
    on p.IdPerson = e.IdPerson 
where root != p.IdPerson 
GROUP BY root; 

1）使用ROWNUM标记每个行中的层次结构
2）使用解析rw - sum(case when lvl > 1 then 1 else 0 end) over(order by rw)定义组（每组是一个层次）
3）tmp_tree_with_root给出与子查询相同的结果CONNECT_BY_ROOT