Выборка из дерева

← →
Mirovodin (2002-05-25 15:05) [0]

Структура таблицы (стандартное дерево)

idx | parentidx | Name |
----------------------------
1 | Null | ВСЕ |
2 | 1 | глава1 |
3 | 2 | подглава |

Необходим запрос выбирающий все элементы Name
начиная с указанного parentID. Наверное необходим рекурсивный
вызов (как при поиске вайлов начиная с каталога), но
SQL изучаю не давно, по этому ... спрашиваю.
Да вложенность, 4-5 уровнней.

← →
Delirium (2002-05-25 15:28) [1]

IMHO: Рекурсивная процедура пишет записи во временную таблицу

← →
Некий (2002-05-26 02:51) [2]

Не понял!
А "WHERE parentidx >= :Myidx" - не подходит?

← →
Mirovodin (2002-05-26 11:19) [3]

2 Некий. Так конечно не подходит т.к. индекс может индексы могут быть совершенно разные, т.е. нужно рекурсию написать но в этом то вся и проблема - плохо знаю SQL.

2 Delirium Нужен пример, я и так понимаю что такое врем-е таблицы, просто ни разу не пользовался ... а в книгах нет :(

← →
Mirovodin (2002-05-27 19:54) [4]

Народ, что ни кто не работал с древовидным представлением данных !!!???

← →
OlegE (2002-05-28 09:49) [5]

На www.ibase.ru есть несколько статей с примерами.

← →
SergSuper (2002-05-28 10:09) [6]

2 Mirovodin
Насчет временных таблиц можно почитать в Book On-Line(это такой хелп к MS SQL) по теме Temporary Tables. Да и в любой книжке должно быть.
А вообще это неплохая задачка для изучающих SQL недавно. Пишется это строчек в 10, используя цикл while, а рекурсия здесь совершенно не нужна (как впрочем и вообще в SQL)

← →
xGrey (2002-05-28 11:33) [7]

Процедура по "вытаскиванию" ветки дерева:
MyId - idx родителя ветки;
TmpTbl - временная таблица;
Table_Name - имя Вашей таблицы.
Код под DB2, но смысл, думаю, будет понятен:
WITH TmpTbl (idx, name, parentidx) as (select idx, name_name, parentidx fromTable_Name where idx=MyId Union All select Table_Name.idx, Table_Name.name, Table_Name.parentidx from TmpTbl , Table_Name where TmpTbl.idx= Table_Name.parentidx) select * from TmpTbl

← →
Mirovodin (2002-05-28 23:09) [8]

1-е нужен только вариант с использованием синтаксиса SQL 7, т.е. функции пользователя не подойдут.
2-е прочитав док-ю я понял, что нужно использовать курсоры и циклы While exests ...

Может я не очень корректно поставил вопрос, но нужно найти как бы все файлы начиная с данного каталога - это по аналогии с FindFist, FindNext. Только в место файлов выступают записи хранящиеся в древ-й структуре.

← →
Cobalt (2002-05-29 02:22) [9]

Вот, у меня что-то такое получилось:

Select Name from MYTABLE
where MyProc(Idx, 3) <>0

Create Procedure MyProc (InpId integer, InpParent integer)
RETURNS (Res integer)
AS DECLARE VARIABLE i1 integer;
BEGIN
SELECT parentidx FROM MYTABLE
WHERE idx = :InpId
INTO i1;
IF (i1 = 0)
THEN BEGIN
Res = 0;
SUSPEND;
END
IF (i1 = InpParent)
THEN BEGIN
Res = 1;
SUSPEND;
END
EXECUTE PROCEDURE MyProc :i1, :InpParent RETURNING_VALUES :res;
SUSPEND;
END

вот только select не получается ;((
Грит, что не может найти function MyProc (IB 5.1.1.680)

← →
Владислав (2002-05-29 06:27) [10]

http://sdm.viptop.ru/articles/sqltrees.html
Эту ссылку видели?
Если это в проекте, то не еще поздно :)

← →
SergSuper (2002-05-29 10:33) [11]

2 Mirovodin
курсоры то зачем? забудьте про них пока

надо делать примерно так:
declare @id int -- заданный элемент

create table #t(id int, level int) -- временная таблица
declare @L int -- текущий уровень элементов
set @L=0
insert #t select @id, 0

while exists(select * from #t t, tbl x where t.id=x.parentidx and t.level=@L)
begin
insert #t
select x.idx, @L+1
from #t t, tbl x
where t.id=x.parentidx and t.level=@L
set @L=@L+1
end

select x.Name from #t t, tbl x where t.id=x.idx

я не проверял, возможны ошибки, главное - поймите принцип

← →
Delirium (2002-05-29 11:31) [12]

Есть таблица Test (id int, Pid int, Val char(10))
далее запрос и рекурсивная процедура, как я и говорил. Можно в принципе и без курсора обойтись - удалением из другой временной таблицы, но это IMHO не оптимальный путь.
select top 0 * into #Tmp from Test exec MyProc 1 select * from Test where id=1 union all select * from #Tmp

CREATE procedure MyProc @paramID int AS declare @id int declare @Pid int declare @Val char(10) declare curTest cursor local scroll for select * from Test where Pid=@ParamID open curTest fetch first from curTest into @id, @Pid, @Val while (@@FETCH_STATUS=0) begin insert #Tmp values (@id, @Pid, @Val) exec MyProc @id fetch next from curTest into @id, @Pid, @Val end close curTest deallocate curTest

← →
Просто так (2002-05-29 11:41) [13]

Хм... Не знал что в MSSQL так проблемно дерево построить... на Oracle гораздо проще...

← →
Delirium (2002-05-29 11:48) [14]

> Просто так

Это чем-же на Oraclе проще ?

← →
fnatali (2002-05-29 12:05) [15]

Delirium © (29.05.02 11:48)
На Oracle это будет выглядеть так:
select idx from dept start with idx=:par1 connect by prior idx=parentidx
Один оператор - очень удобно

← →
Sam3D (2002-05-29 12:18) [16]

В свое время писал для IB процедурку, которая возвращала иерархию вида
1. Param1 1.1. Param2 1.1.1. Param3 1.2. Param4 1.2.1. Param5 1.2.2. Param6... и т.д.
Думаю, для MSSQL не составит особого труда переписать в случае надобности. Конечно, за оптимальность приведенного ниже кода не ручаюсь, но, тем не менее, работало все как надо...
SET TERM !!; CREATE PROCEDURE BUILD_HIERARCHY (PARM SMALLINT, INIHIER VARCHAR(40) CHARACTER SET WIN1251, CNT SMALLINT) RETURNS (PARMID SMALLINT, HIERNUM VARCHAR(40) CHARACTER SET WIN1251) AS DECLARE VARIABLE NEWHIER VARCHAR(40); DECLARE VARIABLE PPARM SMALLINT; DECLARE VARIABLE PARMCOUNT SMALLINT; DECLARE VARIABLE OLDCOUNT SMALLINT; BEGIN PARMID = PARM; NEWHIER = INIHIER || CNT || "."; HIERNUM = NEWHIER; SUSPEND; PARMCOUNT = 1; FOR SELECT ID FROM PAIRS WHERE PARENT = :PARM INTO :PPARM DO BEGIN IF (EXISTS (SELECT * FROM BUILD_HIERARCHY :PPARM, :NEWHIER, :PARMCOUNT))) THEN BEGIN OLDCOUNT = PARMCOUNT; FOR SELECT * FROM BUILD_HIERARCHY :PPARM, :NEWHIER, :PARMCOUNT) INTO :PARMID, :HIERNUM DO BEGIN PARMCOUNT = PARMCOUNT+1; SUSPEND; END PARMCOUNT = OLDCOUNT + 1; END END END!! SET TERM ;!!
У процедуры три параметра вызова: идентификатор корневого элемента, строка, выводимая перед каждым номером иерархии, и начальный номер иерархии.
Возвращает процедура иерархический номер и идентификатор элемента, которому он соответствует.
Таблица, по которой строится иерархия называется PAIRS и имеет два поля - ID и PARENT. Названия говорят сами за себя.
Удачи!

← →
Delirium (2002-05-29 14:04) [17]

> fnatali

Да, в синтаксис Transact SQL не заложено таких операторов, однако это не недостаток, а отсутствие избыточных средств. Ведь ничего не стоит написать данную функцию самостоятельно, причём, возможно - более гибко.

← →
fnatali (2002-05-29 14:17) [18]

>Delirium © (29.05.02 14:04)
Согласна. Но если уж в Оракл предусмотрели подобную конструкцию, то почему бы ею не пользоваться? :)

← →
SergSuper (2002-05-29 15:22) [19]

2 Delirium
Ну-ну
попробуйте написать такую функцию

не получиться, это именно конструкция языка, а не функция, и весьма полезная конструкция
я бы скорее курсоры назвал избыточным средством

← →
Delirium (2002-05-29 19:49) [20]

> SergSuper

Напишу - оплатишь?

← →
SergSuper (2002-05-30 09:30) [21]

>Delirium
ага, пивом :)

на самом деле это именно конструкция, потому что она заставляет выполнять запрос циклически, и просто функцией, которая проверяет какое-то условие здесь не обойтись

я сам работаю с MS SQL, но всё-таки надо признать(хоть мне тоже не хочется) что у Oracle возможностей (и не только избыточных) мягко говоря побольше

← →
Delirium (2002-05-30 10:21) [22]

> SergSuper

Конечно, ораклоиды могут радоваться :(
хотя в конкретном случае, если подойти системно и грамотно проработать вопрос, IMHO, вполне можно обойтись может не одной, так двумя - тремя пользовательскими функциями. Причём, повторюсь, если заняться серьёзно (например, добавить временные индексы или попробовать решить задачу посредством SQL-DMO) - можно и производительность довести до оракловского уровня.

← →
Mirovodin (2002-05-31 09:09) [23]

Большое спасибо Delirium, только твой пример и работает. Так что если кому потребуется - процедура работает на 100%, проверялась на SQL 7.0 и SQL 2000. Хотя если писать для 2000 можно избавится от временной таблицы и заюзать функции... На счет остальных примеров - возможно они с ошибками или для других диалектов, но перенести в SQL 7 мне их не удалось.

← →
Delirium (2002-05-31 13:58) [24]

> Mirovodin

Я тут подумал на досуге - получилось красиво и просто:

select * into #tmp from test where id=1 while exists(select * from test where Pid in (select id from #tmp) and id not in (select id from #tmp)) insert #tmp select * from test where Pid in (select id from #tmp) and id not in (select id from #tmp) select * from #tmp

И ни каких процедур и курсоров :)

← →
Mirovodin (2002-06-01 11:23) [25]

Большое спасибо Delirium, последний запрос работает на несколько поряднов быстрее (таблица ~5000 записей).

Выборка из дерева Найти похожие ветки