|
Движения по связям (на подобие дерева)
#39445819
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
with TAB1 as ( select 99991 as id, 5 as qty from dual union all
select 99992 as id, 10 as qty from dual union all
select 99993 as id, 15 as qty from dual
),
TAB2 as (select 77771 as id, 'doc3' as name, 88881 as p_id from dual union all
select 88881 as id, 'order' as name, 2 as p_id from dual union all
select 2 as id, 'doc1' as name, 3 as p_id from dual union all
select 3 as id, 'doc2' as name, 99991 as p_id from dual union all
select 88882 as id, 'order' as name, 8 as p_id from dual union all
select 88883 as id, 'order' as name, 8 as p_id from dual union all
select 8 as id, 'order' as name, 9 as p_id from dual union all
select 9 as id, 'doc1' as name, 10 as p_id from dual union all
select 10 as id, 'doc2' as name, 99992 as p_id from dual
),
TAB3 as ( select 88881 as id, 5 as qty1 from dual union all
select 88882 as id, 10 as qty1 from dual union all
select 88883 as id, 11 as qty1 from dual union all
)
получить нужно
1.
2.
3.
88881,5 qty1, 99991 ,5 qty
88882,10 qty1, 99992 ,10 qty
88883,11 qty1, 99992 ,10 qty
1.
2.
3.
4.
5.
SELECT id, CONNECT_BY_ROOT p_id p_id
FROM TAB2
WHERE name = 'order' AND CONNECT_BY_ISLEAF = 1
START WITH p_id = (вот как сюда подставлять данные из TAB1 (она большая и на нее могут накладываются еще дополнительные фильтра (Дата)))
CONNECT BY NOCYCLE PRIOR p_id = id
|
|
|
