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

[junjie_2006]

有两个表 一个表是供应商 它里面存储的父子层级关系，就是说一个大的供应商下面有很多下属小供应商
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

[hebo2005]

select root_nm,sum(amt)
   from 
    (
select a.provider_name,sys_connect_by_root(a.provider_name) root_nm,nvl(b.amount,0) amt
    from supply a,invoice b
     where a.provider_code=b.provider_code (+)
     start with a.super_provider_code  is null
     connect by prior a.provider_code  =a.super_provider_code
     )
group by root_nm

[hebo2005]

SELECT   root_nm, SUM (amt)
    FROM (SELECT     a.provider_name,
                     sys_connect_by_root (a.provider_name) root_nm,
                     NVL (b.amount, 0) amt
                FROM supply a, invoice b
               WHERE a.provider_code = b.provider_code(+)
          START WITH a.super_provider_code IS NULL
          CONNECT BY PRIOR a.provider_code = a.super_provider_code)
GROUP BY root_nm

[cosio]

按逻辑,005那两个地方应该是004!

[junjie_2006]

sys_connect_by_root (a.provider_name) root_nm 提示 sys_connect_by_root 无效

[cosio]

select a02,a.BB from
(
select nvl(a03,a01) TT,sum(b02) BB, row_number() over(partition by nvl(a03,a01) order by nvl(a03,a01))
 from t_a,t_b where a01=b03(+)
 group by nvl(a03,a01)
 ) A left outer join
 t_a on a.tt=a01

[cosio]

按逻辑,005那两个地方应该是004!

create table t_a
(
       a01  varchar2(5)
       ,a02 varchar2(20)
       ,a03  varchar2(5)
)
create table t_b
(
       b01  varchar2(5)
       ,b02 varchar2(20)
       ,b03  varchar2(5)
)
insert into t_a values('001','A一级供应商','');
insert into t_a values('002','A二级供应商','001');   
insert into t_a values('003','A二级供应商','001') ;   
insert into t_a values('004','B一级供应商','');
insert into t_a values('005','B二级供应商','004');    
insert into t_a values('006','B二级供应商','004') ;   
  
insert into t_b values('IN001','50','002') ; 
insert into t_b values('IN002','70','002') ;
insert into t_b values('IN003','60','005') ;
insert into t_b values('IN004','40','006') ;
insert into t_b values('IN005','85','001') ;

--SQL:
select a02,a.BB from
(
select nvl(a03,a01) TT,sum(b02) BB, row_number() over(partition by nvl(a03,a01) order by nvl(a03,a01))
 from t_a,t_b where a01=b03(+)
 group by nvl(a03,a01)
 ) A left outer join
 t_a on a.tt=a01


--RESULT:

A一级供应商	205
B一级供应商	100

[hyrongg]

版本是9i吧？
10G用connect by nocycle

[cosio]

[Quote=引用 4 楼 junjie_2006 的回复:]
sys_connect_by_root (a.provider_name) root_nm 提示 sys_connect_by_root 无效
[/Quote]

你oracle的版本应该是低于10G的!所以提示这个函数无效!

[junjie_2006]

我的是9i的版本

[oracledbalgtu]

 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');
 
SELECT P.PROVIDER_NAME,SUM(I.AMOUNT) 合计
  FROM INVOICE I,
       (SELECT PROVIDER_NAME, PROVIDER_CODE
          FROM SUPPLY
         WHERE SUPER_PROVIDER_CODE IS NULL) P
 WHERE I.PROVIDER_CODE IN
       (SELECT PROVIDER_CODE
          FROM SUPPLY
         START WITH PROVIDER_CODE = P.PROVIDER_CODE
        CONNECT BY PRIOR PROVIDER_CODE = SUPER_PROVIDER_CODE)
 GROUP BY PROVIDER_NAME;
/*
输出:
PROVIDER_NAME	合计
A一级供应商	205
B一级供应商	100
*/

[Quote=引用楼主 junjie_2006 的帖子:]
有两个表 一个表是供应商 它里面存储的父子层级关系，就是说一个大的供应商下面有很多下属小供应商
supply 结构如下
provider_code provider_name super_provider_code
001 A一级供应商 null
002 A二级供应商 001
003 A二级供应商 001
004 B一级供应商 null
005 B二级供应商 …
[/Quote]

[cosio]

问你那个表中的数据是不是004?,如果005好像逻辑不通!

[junjie_2006]

是写错了，应该是004

[cosio]

那9i的,就可以用我的那个SQL,PASS!

把SQL改为你用的表名!直接拷PL/SQL中运行PASS

select c.provider_name,d.a02 from
(
 select nvl(super_provider_code,a.provider_code) a01 ,sum(b.amount) a02 , 
 row_number() over(partition by  nvl(super_provider_code,a.provider_code) order by  nvl(super_provider_code,a.provider_code))
 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


A一级供应商    205
B一级供应商    100

[cosio]

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

认真看一下,row_number不用调用!所以............可以delete!语法如上!