有一个多叉树形的数据结构,假如有4层,数据有层次字段和父节点字段(没有子节点字段)和一个数字字段,1级最大,4级最小,现在只有最下层(即第四层)数字字段有内容,我想把这个表上面的3层,1,2,3层的这个数字字段也赋值(为其所有子节点的数据之和),其中关联条件还要和另外一个表关联,应该怎么做呢?
有个同事说可以select sum(br_bal) from...where c.branch_grade = '4' group by c.father_branch从最下层按照父节点group by,但是之后怎么同步写入到这个表里呢,我想像下面这样下,但是明显有问题哎for nlevel in 4..1
update ods_hx_gl_bal,co_branch set dr_bal = (
select sum(dr_bal) from ods_hx_gl_bal g, co_branch c
WHERE g.acc_hrt IN ('2002', '2011', '2022', '2023', '2020')
AND g.cur_no = '01'
AND c.branch_code = g.br_no
AND c.branch_grade = nlevel
AND (g.tx_date / 100) = (TO_NUMBER('20101131') / 100))
WHERE ods_hx_gl_bal.acc_hrt IN ('2002', '2011', '2022', '2023', '2020')
AND ods_hx_gl_bal.cur_no = '01'
AND co_branch.branch_code = ods_hx_gl_bal.br_no
AND co_branch.branch_grade = nlevel
AND (ods_hx_gl_bal.tx_date / 100) = (TO_NUMBER('20101131') / 100);
有个同事说可以select sum(br_bal) from...where c.branch_grade = '4' group by c.father_branch从最下层按照父节点group by,但是之后怎么同步写入到这个表里呢,我想像下面这样下,但是明显有问题哎for nlevel in 4..1
update ods_hx_gl_bal,co_branch set dr_bal = (
select sum(dr_bal) from ods_hx_gl_bal g, co_branch c
WHERE g.acc_hrt IN ('2002', '2011', '2022', '2023', '2020')
AND g.cur_no = '01'
AND c.branch_code = g.br_no
AND c.branch_grade = nlevel
AND (g.tx_date / 100) = (TO_NUMBER('20101131') / 100))
WHERE ods_hx_gl_bal.acc_hrt IN ('2002', '2011', '2022', '2023', '2020')
AND ods_hx_gl_bal.cur_no = '01'
AND co_branch.branch_code = ods_hx_gl_bal.br_no
AND co_branch.branch_grade = nlevel
AND (ods_hx_gl_bal.tx_date / 100) = (TO_NUMBER('20101131') / 100);
表ods_hx_gl_bal是储存一个多叉树的表,从根节点到叶子节点一共有4层,这个表的数据结构是这样的,这次逻辑涉及的字段有一个数字字段d,一个主键,一个层级标志(1,2,3,4层,这个字段是co_branch里面的,两者通过c.branch_code = g.br_no关联)和一个字段用于储存其父节点,现在是只有叶子节点即第四层的数字字段d有值,我希望求出ods_hx_gl_bal表里上面3层的数据字段d,其中每一层的父节点等于他的子节点之和
和需要的结果集 看你的需求挺难懂的
---------- ----------
1
2
2
3 1
3 2
3 3
然后
declare
num number := 2;
begin
for i in 1 .. 2 loop
if num <> 0 then
dbms_output.put_line(num);
update test
set au = (select sum(au) from test where ti = num + 1)
where ti = num;
commit;
num := num - 1;
end if;
end loop;
end;得到
SQL> select * from test;TI AU
---------- ----------
1 12
2 6
2 6
3 1
3 2
3 3感觉和你那差不多 呵呵