+++++++++++++++++++++++++++++++++++
有两个表 一个表是供应商 它里面存储的父子层级关系，就是说一个大的供应商下面有很多下属小供应商
supply 结构如下
provider_code provider_name super_provider_code
001 A一级供应商 null
002 A二级供应商 001
003 A二级供应商 001
004 B一级供应商 null
005 B二级供应商 005
006 B二级供应商 005
另外一个表是发票表 每张发票里面 有记录一个供应商的provider_code
invoice 结构如下
invoice_no amount provider_code
IN001 50 002
IN002 70 002
IN003 60 005
IN004 40 006
IN005 85 001
现在只想知道每个一级供应商（包括下属的供应商)总金额 想得到如下结果
A一级供应商 205
B一级供应商 100
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
搭建测试环境:
DROP TABLE supply;
CREATE TABLE supply
(provider_code VARCHAR2(10),provider_name VARCHAR2(20),super_provider_code VARCHAR2(10));
DROP TABLE invoice;
CREATE TABLE invoice (invoice_no VARCHAR2(10), amount INT, provider_code VARCHAR2(10));
insert into supply values( '001','A一级供应商',null );
insert into supply values( '002','A二级供应商','001' );
insert into supply values( '003','A二级供应商','001' );
insert into supply values( '004','B一级供应商',null );
insert into supply values( '005','B二级供应商','004' );
insert into supply values( '006','B二级供应商','004' );
insert into invoice values( 'IN001','50','002');
insert into invoice values( 'IN002','70','002');
insert into invoice values( 'IN003','60','005');
insert into invoice values( 'IN004','40','006');
insert into invoice values( 'IN005','85','001');
--SQL:
select c.provider_name,d.a02 from
(
select nvl(super_provider_code,a.provider_code) a01 ,sum(b.amount) a02
from supply a,invoice b where a.provider_code=b.provider_code(+)
group by nvl(super_provider_code,a.provider_code)
) d ,supply c WHERE c.provider_code=d.a01
--result:
A一级供应商 205
B一级供应商 100