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

Question

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

按逻辑,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

Answer 6

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

Answer 7

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

Answer 8

 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
*/

Answer 9

那9i的,就可以用我的那个SQL,PASS!把SQL改为你用的表名!直接拷PL/SQL中运行PASSselect 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

Answer 10

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!语法如上!