问题描述
- oracle 统计销售员销售业绩的sql(级联累加)
-
销售员表
id name 销售额(万) 上级id
1 A 102 B 10 1
3 C 10 2
4 D 10 2
5 E 106 F 10 5
最后实现的结果是 id为1 的 总销售额是 40万 2的销售额 是30 万 3 的销售额10万 5的销售额是10 20万 。。。。。
就是从树的叶子开始依次将子节点的值加到父节点上
解决方案
create table xx_sale
(
id number primary key,
name varchar2(20),
amount number,
pre_id number
)
select x.id, sum(x.amount)
from xx_sale x
connect by prior x.pre_id = x.id
group by x.id
解决方案二:
我给个思路,按你的需求,可以分别建立组织机构表(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对不同的组织机构级别进行聚合,可得到你想要的结果。
我认为这个简单直观,网上还有使用存储过程实现的方法,你有兴趣,可以研究一下。
解决方案三:
纠正一下,将sales_tb与level_vw通过id_3关联