1 скалярным подзапросом
| Код |
SQL> with summs as (
2 select 1 numb, 101 iekr_id, 100 amount from dual
3 union all select 1 numb, 102 iekr_id, 10 amount from dual
4 union all select 1 numb, 201 iekr_id, 1 amount from dual
5 union all select 2 numb, 101 iekr_id, 200 amount from dual
6 union all select 2 numb, 201 iekr_id, 20 amount from dual
7 union all select 3 numb, 101 iekr_id, 300 amount from dual
8 union all select 3 numb, 101 iekr_id, 3 amount from dual
9 union all select 3 numb, 102 iekr_id, 30 amount from dual
10 )
11 ,iekr as (
12 select 0 id, null parent_id from dual
13 union all select 10 id, 0 parent_id from dual
14 union all select 20 id, 0 parent_id from dual
15 union all select 101 id, 10 parent_id from dual
16 union all select 102 id, 10 parent_id from dual
17 union all select 201 id, 20 parent_id from dual
18 )
19 select
20 s.numb
21 ,t.id iekr_id
22 ,t.parent_id
23 ,s.amount
24 ,(select sum(ss.amount)
25 from iekr tt
26 left join summs ss partition by (ss.numb) on tt.id = ss.iekr_id
27 start with tt.id = t.id and ss.numb = s.numb
28 connect by prior tt.id = tt.parent_id and prior ss.numb = ss.numb
29 ) scalar_sum
30 from iekr t
31 left join summs s partition by (s.numb) on t.id = s.iekr_id
32 start with t.id = 0
33 connect by prior t.id = t.parent_id and prior s.numb = s.numb
34 ;
NUMB IEKR_ID PARENT_ID AMOUNT SCALAR_SUM
---------- ---------- ---------- ---------- ----------
1 0 111
1 10 0 110
1 101 10 100 100
1 102 10 10 10
1 20 0 1
1 201 20 1 1
2 0 220
2 10 0 200
2 101 10 200 200
2 102 10
2 20 0 20
2 201 20 20 20
3 0 333
3 10 0 333
3 101 10 300 303
3 101 10 3 303
3 102 10 30 30
3 20 0
3 201 20
19 rows selected
|
2 моделью
| Код |
19 select numb,iekr_id,parent_id, amount, summ_amount
20 from iekr t
21 left join summs s partition by (s.numb) on t.id = s.iekr_id
22 start with t.id = 0
23 connect by prior t.id = t.parent_id and prior s.numb = s.numb
24 model dimension by (s.numb,t.id iekr_id, t.parent_id, rownum rn)
25 measures (level lvl, amount, 0 summ_amount)
26 (summ_amount[any,any,any,any] order by lvl desc,rn = nvl(sum(nvl(summ_amount,0))[cv(),any,cv(iekr_id),any],0)+nvl(amount[cv(),cv(),cv(),cv()],0)
27 )
28 order by rn
29 ;
NUMB IEKR_ID PARENT_ID AMOUNT SUMM_AMOUNT
---------- ---------- ---------- ---------- -----------
1 0 111
1 10 0 110
1 101 10 100 100
1 102 10 10 10
1 20 0 1
1 201 20 1 1
2 0 220
2 10 0 200
2 101 10 200 200
2 102 10 0
2 20 0 20
2 201 20 20 20
3 0 333
3 10 0 333
3 101 10 300 300
3 101 10 3 3
3 102 10 30 30
3 20 0 0
3 201 20 0
19 rows selected |
Оба варианта - не айс |
)
