oracle 统计销售员销售业绩的sql（级联累加）

Question

销售员表
id name 销售额(万) 上级id
1 A 10

2 B 10 1
3 C 10 2
4 D 10 2
5 E 10

6 F 10 5

最后实现的结果是 id为1 的 总销售额是 40万 2的销售额 是30 万 3 的销售额10万 5的销售额是10 20万 。。。。。
就是从树的叶子开始依次将子节点的值加到父节点上

Answer 1

我给个思路，按你的需求，可以分别建立组织机构表（organization_tb)、销售业绩表(sale_tb)。
表结构如下：
oraganization_rb(id,name,id_upper)
sale_tb(id,sales,……)
根据组织结构表建立视图level_vw，建立3级组织机构，SQL语句如下：
SELECT id AS id_1,name AS name_1,id AS id_2,name AS name_2,id AS id_3,name AS name_3 FROM sales_tb WHERE (id_upper IS NULL)
UNION ALL SELECT b.id_1,b.name_1,a.id AS id_2,a.name AS name_2,a.id AS id_3,name AS name_3 FROM sales_tb a,(SELECT id AS id_1,name AS name_1 FROM sales_tb WHERE (id_upper IS NULL)) b WHERE a.id_upper=b.id_1
UNION ALL SELECT d.id_1,d.name_1,d.id_2,d.name_2,c.id AS id_3,c.name AS name_3 FROM sales_tb c,(SELECT b.id_1,b.name_1,a.id AS id_2,a.name AS name_2 FROM sales_tb a,(SELECT id AS id_1,name AS name_1 FROM sales_tb WHERE (id_upper IS NULL)) b WHERE a.id_upper=b.id_1) d WHERE c.id_upper=d.id_2

将sales_tb与oraganization通过id关联，用groud by对不同的组织机构级别进行聚合，可得到你想要的结果。
我认为这个简单直观，网上还有使用存储过程实现的方法，你有兴趣，可以研究一下。