Averi
Дата: 01.03.2007 15:32:14
Необходимо построить эффективный запрос , возвращающий дерево
(дерево должно строиться ровно один раз).
Нужно вернуть только те ветки деревьев, которые в качестве листьев
имеют значение поля what='item', остальные отбросить.
Данный запрос имеется (см. ниже), но достаточно не эффективный, потому
что приходиться 2 раза строить дерево (перевернуть туда-обратно).
--creating table
CREATE TABLE tmenu
(
id_menu NUMBER,
id_parent NUMBER,
what VARCHAR2(4));
--inserting test data
insert into tmenu (id_menu,id_parent,what)
values (21101,21001,'item');
insert into tmenu (id_menu,id_parent,what)
values (71001,11001,'menu');
insert into tmenu (id_menu,id_parent,what)
values (51001,21001,'item');
insert into tmenu (id_menu,id_parent,what)
values (21001,null,'menu');
insert into tmenu (id_menu,id_parent,what)
values (31001,21001,'item');
insert into tmenu (id_menu,id_parent,what)
values (110001,null,'item');
insert into tmenu (id_menu,id_parent,what)
values (41001,21001,'item');
insert into tmenu (id_menu,id_parent,what)
values (11002,11001,'menu');
insert into tmenu (id_menu,id_parent,what)
values (11001,null,'menu');
insert into tmenu (id_menu,id_parent,what)
values (11201,null,'menu');
--В результате должны получить след:
LEVEL ID_MENU ID_PARENT WHAT
------------------------------------------------------------
1 110001 item
1 21001 menu
2 51001 21001 item
2 21101 21001 item
2 31001 21001 item
2 41001 21001 item
--Есть запрос, для получения для данного результата, но в нем необходимо 2 раза
--строить дерево:
select level, v.id_menu, v.id_parent, what
from
(select distinct * from tmenu
start with what='item'
connect by prior id_parent=id_menu) v
start with id_parent is null
connect by prior id_menu=id_parent
Elic
Дата: 01.03.2007 16:37:24
| Averi |
| Есть запрос, для получения для данного результата, но в нем необходимо 2 раза строить дерево |
А почему это плохо? :)
На тебе только одно построение дерева:
with t as
( select level as lvl, id_menu, id_parent, what, sys_connect_by_path(id_menu, '\') as path
from tmenu
start with id_parent is null
connect by id_parent = prior id_menu
)
select distinct t1.lvl, t1.id_menu, t1.id_parent, t1.what
from t t1, t t2
where t2.what = 'item'
and t2.path like t1.path || '%'
;
LVL ID_MENU ID_PARENT WHAT
------------- ------------- ------------- ----
1 21001 menu
1 110001 item
2 21101 21001 item
2 31001 21001 item
2 41001 21001 item
2 51001 21001 item
6 rows selected.with t as
( select level as lvl, id_menu, id_parent, what, sys_connect_by_path(id_menu, '\') as path, rownum as rn
from tmenu
start with id_parent is null
connect by id_parent = prior id_menu
)
select t1.lvl, t1.id_menu, t1.id_parent, t1.what
from t t1
where exists
( select null from t t2
where t2.what = 'item'
and t2.path like t1.path || '%'
)
order by t1.rn
;
LVL ID_MENU ID_PARENT WHAT
------------- ------------- ------------- ----
1 21001 menu
2 21101 21001 item
2 51001 21001 item
2 31001 21001 item
2 41001 21001 item
1 110001 item
6 rows selected.
Vladimir Sitnikov
Дата: 02.03.2007 02:19:33
Скучно. Стандартный запрос -- стандарное решение :-/
select *
from (select level as lvl,
id_menu,
id_parent,
what,
max(what) keep(dense_rank last order by level) over(partition by rownum - level) type
from tmenu
start with id_parent is null
connect by id_parent = prior id_menu)
where type = 'item'
PS. Для пущей 'оптимизации', следует сделать index on tmenu(decode(id_parent,null,1)) и заменить условие в start with на нормальное
PPS. А ещё лучше, наверное, сделать mview, в котором хранится уже неважно каким путём полученный результат.
Elic
Дата: 02.03.2007 08:48:10
| Vladimir Sitnikov |
| Скучно. Стандартный запрос -- стандарное решение :-/ |
Тебе рано скучать и называть "стандартным решением" свою поделку, способную разве что продемонстрировать магию исходных данных
Работай над собой:
insert into tmenu (id_menu,id_parent,what) values (21100,21001,'menu');
insert into tmenu (id_menu,id_parent,what) values (21101,21001,'item');
...
insert into tmenu (id_menu,id_parent,what) values (11201,null,'menu');
Vladimir Sitnikov
Дата: 02.03.2007 23:29:17
| Elic |
Тебе рано скучать и называть "стандартным решением" свою поделку, способную разве что продемонстрировать магию исходных данных
Работай над собой: |
Это точно. Как можно было подумать, что rownum-level позволит что-то полезное получить? ;-)
Тем не менее, задача стандартной не перестаёт быть. Она сводится к "нахождению суммы выражения
decode(what, 'item', 1, 0)
по всем дочерним узлам любой вложенности и фильтрации по summ_of_decode>0". Верно?
Считаю разумным не выкладывать решение, до того как оно появится
здесь
Elic
Дата: 03.03.2007 17:53:51
| Stax. |
| имхо надо еще проверять на CONNECT_BY_ISLEAF |
В принципе ты прав. Только опровергающий пример неудачный привёл.
IMO, задачу автора следует понимать как "вернуть только те ветки деревьев, которые
хотя бы одним листом имеют значение поля what='item'" или "Не выводить меню без элементов".
| Stax. |
| Elic | | На тебе только одно построение дерева: |
+ проверте на "не лист" |
Да, я тоже это не учёл.
Но, IMHO, это и не надо. Если додумать физическую интерпретацию данных, то станет ясно, что what='item' уже само по себе является призноком листа.