oracle层级关系表查询,oracle一张层级关系表和另一张表汇总

+++++++++++++++++++++++++++++++++++

有两个表  一个表是供应商  它里面存储的父子层级关系，就是说一个大的供应商下面有很多下属小供应商

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

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

搭建测试环境:

DROPTABLEsupply;CREATETABLEsupply

(provider_codeVARCHAR2(10),provider_nameVARCHAR2(20),super_provider_codeVARCHAR2(10));DROPTABLEinvoice;CREATETABLEinvoice  (invoice_noVARCHAR2(10),   amountINT,   provider_codeVARCHAR2(10));insertintosupplyvalues('001','A一级供应商',null);insertintosupplyvalues('002','A二级供应商','001');insertintosupplyvalues('003','A二级供应商','001');insertintosupplyvalues('004','B一级供应商',null);insertintosupplyvalues('005','B二级供应商','004');insertintosupplyvalues('006','B二级供应商','004');insertintoinvoicevalues('IN001','50','002');insertintoinvoicevalues('IN002','70','002');insertintoinvoicevalues('IN003','60','005');insertintoinvoicevalues('IN004','40','006');insertintoinvoicevalues('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