Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Упростить селект  [new]
люди добрые...
Guest
Ув. форумчане, нужна ваша помощь.
Есть 3 таблички, в упрощенном виде выглядят так:
create table main_tbl
(i_id   number,
 m_id   number);

create table sub_tbl
(m_id number);

create table sub_tbl_values
(m_id number
,s_id number
,s_value varchar2(5));

insert into main_tbl (i_id, m_id) values (1, 1);
insert into main_tbl (i_id, m_id) values (2, 6);
insert into main_tbl (i_id, m_id) values (3, 2);
insert into main_tbl (i_id, m_id) values (4, 4);

insert into sub_tbl (m_id) values (1);
insert into sub_tbl (m_id) values (44);
insert into sub_tbl (m_id) values (6);
insert into sub_tbl (m_id) values (77);
insert into sub_tbl (m_id) values (999);
insert into sub_tbl (m_id) values (2);
insert into sub_tbl (m_id) values (88);
insert into sub_tbl (m_id) values (55);
insert into sub_tbl (m_id) values (888);
insert into sub_tbl (m_id) values (33);
insert into sub_tbl (m_id) values (4);
insert into sub_tbl (m_id) values (11);

insert into sub_tbl_values (m_id, s_id, s_value) values (1, null, 'aaa');
insert into sub_tbl_values (m_id, s_id, s_value) values (1, null, 'bbb');
insert into sub_tbl_values (m_id, s_id, s_value) values (2, 33, '');
insert into sub_tbl_values (m_id, s_id, s_value) values (2, 44, 'eee');
insert into sub_tbl_values (m_id, s_id, s_value) values (2, 55, '');
insert into sub_tbl_values (m_id, s_id, s_value) values (33, null, 'bbb');
insert into sub_tbl_values (m_id, s_id, s_value) values (44, null, 'ddd');
insert into sub_tbl_values (m_id, s_id, s_value) values (55, null, 'zzz');
insert into sub_tbl_values (m_id, s_id, s_value) values (6, 77, '');
insert into sub_tbl_values (m_id, s_id, s_value) values (6, null, 'jjj');
insert into sub_tbl_values (m_id, s_id, s_value) values (77, 999, 'ttt');
insert into sub_tbl_values (m_id, s_id, s_value) values (999, null, 'bbb');
insert into sub_tbl_values (m_id, s_id, s_value) values (1, null, 'bbb');
insert into sub_tbl_values (m_id, s_id, s_value) values (6, 88, '');
insert into sub_tbl_values (m_id, s_id, s_value) values (88, 888, '');
insert into sub_tbl_values (m_id, s_id, s_value) values (888, null, 'iii');
insert into sub_tbl_values (m_id, s_id, s_value) values (4, null, 'ppp');
insert into sub_tbl_values (m_id, s_id, s_value) values (4, 11, '');
insert into sub_tbl_values (m_id, s_id, s_value) values (11, null, 'lll');


Связь главной таблицы main_tbl с таблицей значений sub_tbl_values идет через m_id.
Все m_id также находятся в таблице sub_tbl;
И есть связь между строками sub_tbl_values через иерархию m_id = s_id предыдущей строки.

Нужно найти все строки из main_tbl для которых в sub_tbl_values есть строки с s_value = 'bbb';

Результат:
I_ID   M_ID
1      1
2      6
3      2


Сделал таким селектом:
select * 
from main_tbl m
where m.m_id in
(select (select min(stv1.m_id) keep(dense_rank first order by level desc)
         from   sub_tbl_values stv1
         where 1 = 1
         start with stv1.m_id = stv.m_id
         connect by  stv1.s_id = prior stv1.m_id) root
  from sub_tbl_values stv
 where stv.s_value = 'bbb');


Можно ли сделать как-то попроще?

Как один из вариантов нахождения root пробовал сделать через first_value.
Для селекта:
select first_value(stv.m_id) over (order by level) root
      ,stv.*
from sub_tbl_values stv
where 1 = 1
 start with stv.m_id = 6
connect by prior stv.s_id = stv.m_id; 
работает.
А вот если вывести всю иерархию, то уже не то:
select first_value(stv.m_id) over (order by level) root
      ,stv.*
from sub_tbl_values stv
where 1 = 1
 start with stv.m_id in
            (select st.m_id
             from sub_tbl st, main_tbl mt
             where 1 = 1
               and mt.m_id = st.m_id)
connect by prior stv.s_id = stv.m_id; 

Можно ли для последнего селекта (и как) вывести корневой узел для каждой строки?

Спасибо.

К сообщению приложен файл (selects1.sql - 3Kb) cкачать
16 сен 13, 20:43    [14846889]     Ответить | Цитировать Сообщить модератору
 Re: Упростить селект  [new]
-2-
Member

Откуда:
Сообщений: 15330
люди добрые...
Можно ли для последнего селекта (и как) вывести корневой узел для каждой строки?
connect_by_root
16 сен 13, 20:55    [14846919]     Ответить | Цитировать Сообщить модератору
 Re: Упростить селект  [new]
люди добрые...
Guest
connect_by_root

Спасибо!
Прикольно, connect_by в книжке был, а этой конструкции не было.
Для общего развития - можно первый селект переписать проще или по-другому, без connect_by_root
17 сен 13, 13:06    [14848933]     Ответить | Цитировать Сообщить модератору
 Re: Упростить селект  [new]
Elic
Member

Откуда:
Сообщений: 29980
люди добрые...
по-другому, без connect_by_root
STFF чем заменить connect_by_root
17 сен 13, 13:35    [14849193]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить