求个递归查询

-一个大坑 2019-08-09 03:30:15

我想在递归后面加个汇总,图一是数据，计算方法见图二

...全文

169 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

-一个大坑 2019-09-05

打赏
举报

引用 1 楼超叔csdn 的回复:

已解出，加qq99679314

递归查询数据有重复的，而且用父节点查上一层还没查到数据，要怎么办

SELECT bc.matnr,
         bc.idnrk,
         bc.werks,
         bc.posnr,
         bc.menge,
         bc.alpgr,
         bc.alprf,
         level lavela,
         CONNECT_BY_ISLEAF lf,
         func_sum(ltrim(sys_connect_by_path(bc.menge, '*'), '*')) qty_per_rack,
         sys_guid() skey
    FROM sap_bom bc
    WHERE CONNECT_BY_ISLEAF = 1
   START WITH bc.matnr = 'XXXX'
  CONNECT BY PRIOR bc.idnrk = bc.matnr
   order by level, bc.matnr, bc.idnrk

-一个大坑 2019-09-04

打赏
举报

select a, b, func_sum(pathstr) ljz from (select connect_by_root A nodea, connect_by_root B nodeb, A, B, CONNECT_BY_ISLEAF lf, numbera, ltrim(sys_connect_by_path(numbera, '*'), '*') pathstr from testa start with a = 'A' connect by prior b = a) order by a,b;

超叔csdn 2019-08-12

打赏
举报

算了，不藏私了。
--先做一个函数做字符串计算
create or replace function func_sum(str in varchar2)
return number
as
v_num number;
begin
execute immediate 'select '||str||' from dual' into v_num;
return v_num;
end;
/
根据题目要求制造测试数据表
create table testa(a varchar2(10),b varchar2(10),numbera number(10));
insert into testa values('A','AA',2);
insert into testa values('A','AC',2);
insert into testa values('A','AB',2);
insert into testa values('AA','AAA',3);
insert into testa values('AA','AAB',3);
insert into testa values('AC','ACA',3);
insert into testa values('AAB','AABA',4);
insert into testa values('AABA','AABAA',5);
commit;
--这里考虑到排序和过滤，做了一下表关联，否则可以不用做表关联
select B.A,B.B,B.NUMBERA,A.LJZ
from (
select nodea,nodeb,sum(func_sum(pathstr)) ljz
from (
select connect_by_root A nodea,connect_by_root B nodeb, A,B,CONNECT_BY_ISLEAF lf,numbera,ltrim(sys_connect_by_path(numbera,'*'),'*') pathstr
from testa
connect by prior b=a )
where lf=1
group by nodea,nodeb) A join (select A,B,numbera,level lv from testa
start with a='A'
connect by prior b=a ) B on A.nodea=B.A and A.nodeb=B.B
order by B.LV,B.A
;

卖水果的net 2019-08-12

打赏
举报

单一语句实现，有些困难，逻辑会很复杂，建议存储过程来完成。

-一个大坑 2019-08-12

打赏
举报

引用 3 楼超叔csdn 的回复:

算了，不藏私了。 --先做一个函数做字符串计算 create or replace function func_sum(str in varchar2) return number as v_num number; begin execute immediate 'select '||str||' from dual' into v_num; return v_num; end; / 根据题目要求制造测试数据表 create table testa(a varchar2(10),b varchar2(10),numbera number(10)); insert into testa values('A','AA',2); insert into testa values('A','AC',2); insert into testa values('A','AB',2); insert into testa values('AA','AAA',3); insert into testa values('AA','AAB',3); insert into testa values('AC','ACA',3); insert into testa values('AAB','AABA',4); insert into testa values('AABA','AABAA',5); commit; --这里考虑到排序和过滤，做了一下表关联，否则可以不用做表关联 select B.A,B.B,B.NUMBERA,A.LJZ from ( select nodea,nodeb,sum(func_sum(pathstr)) ljz from ( select connect_by_root A nodea,connect_by_root B nodeb, A,B,CONNECT_BY_ISLEAF lf,numbera,ltrim(sys_connect_by_path(numbera,'*'),'*') pathstr from testa connect by prior b=a ) where lf=1 group by nodea,nodeb) A join (select A,B,numbera,level lv from testa start with a='A' connect by prior b=a ) B on A.nodea=B.A and A.nodeb=B.B order by B.LV,B.A ;

可以了，谢谢

超叔csdn 2019-08-09