a4613934 于 2016.04.21 16:17 提问

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

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

3个回答

lvln2000   2016.04.27 16:12

oraganization_rb(id,name,id_upper)
sale_tb(id,sales,……)

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

huangwenkangz   2016.04.21 17: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

lvln2000   2016.04.27 16:13