Рекурсивный иерархический SQL-запрос

← →
European (2004-04-19 16:59) [0]

Здравствуйте, уважаемые!
У меня такая ситуация. Имеется некая организация, имеющая несколько отделов. Каждый отдел имеет несколько филиалов 1 уровня, филиалы 1 уровня могут иметь филиалы 2 уровня и так далее. Теоретически, уровень иерархии не ограничен, но на практике уровней больше 5 скорее всего не будет (пока не предвидится). Ни организация, ни отделы с клиентами не работают. С клиентами работают филиалы любого уровня.
Данные о клиентах содержаться в таблице “Клиенты”, об организации в таблице “Организация”. Иерархия в таблице “Организация” организована при помощи двух полей idOrg и idOrgMaster. idOrgMaster – ссылка на родительский idOrg. Связь таблиц “Клиенты” “Организация” по полю idOrg.
Требуется написать запрос, получающий количество клиентов каждого отдела. Только я не знаю как это сделать.
При помощи “Connect by” получаю уровень вложенности, надо делать рекурсию, но как? Я делал вложенные подзапросы, но они хороши только для ограниченного уровня вложенности.
В И-нете кроме фраз “Простого способа, к сожалению, нет. Приходится организовывать рекурсию запросов” ничего не нашёл. Может кто-поможет?
Заранее благодарю!!!

← →
Johnmen © (2004-04-19 17:01) [1]

ibase.ru
Там есть пример рекурсивной ХП для решения...

← →
European (2004-04-19 17:10) [2]

Что-то не нахожу

← →
Vlad © (2004-04-19 17:14) [3]

>При помощи “Connect by” получаю уровень вложенности
СУБД Оракл, надо понимать ?
Тогда можно так:

SELECT O.*, (select count(*) from Клиенты C where C.idOrg=O.idOrg ) as Cnt
FROM Организации O
START WITH idOrg = <Root>
CONNECT BY PRIOR idOrd=idOrgMaster

← →
European (2004-04-19 17:21) [4]

> СУБД Оракл, надо понимать ?

Да. Упустил самое главное!

← →
European (2004-04-19 17:22) [5]

Гады! Сервак отрубили! Вот и работай после этого! Ладно, вернёмся к этому вопросу в среду.

Может ещё есть какие мнения?

← →
ZrenBy © (2004-04-19 17:30) [6]

Это, конечно, не под Oracle, но запостю. (а вдруг ... (c))
declare @tO table(oId int,oIdMaster int) insert into @tO select 0, null union all select 1, 0 union all select 2, 0 union all select 3, 0 union all select 11, 1 union all select 12, 1 union all select 21, 2 union all select 22, 2 union all select 23, 2 union all select 211, 21 union all select 212, 21 union all select 2121, 212 union all select 2122, 212 declare @tC table (oId int,cId int) insert into @tC select 1, 1 union all select 2, 2 union all select 3, 3 union all select 1, 4 union all select 2, 5 union all select 3, 6 union all select 11, 11 union all select 12, 12 union all select 21, 21 union all select 22, 22 union all select 23, 23 union all select 211, 211 union all select 212, 212 union all select 2121, 2121 union all select 2122, 2122 declare @r table (oID int, L int, Cnt int) declare @ID int set @ID = 2 -- Id нужного отдела declare @L int set @L = 0 insert into @R select @ID,0,(select count(*) from @tC where oID=@ID) while(1=1)begin insert into @R select o.oId,@L+1,(select count(*) from @tC c where c.oID=o.oId) from @tO o inner join @R r on r.oID=o.oIDMaster and L=@L if(@@rowcount = 0)break set @L = @L + 1 end select sum(Cnt) from @r

← →
VAleksey © (2004-04-19 18:07) [7]

В ORACLE это решается с помощью ключевых слов START WITH и CONNECT BY.

← →
Матлабист (2004-04-19 18:17) [8]

Документация по Oracle, Hierarchical Queries
Там все понятно написано

← →
European (2004-04-20 15:21) [9]

> Документация по Oracle, Hierarchical Queries
> Там все понятно написано

Вы имеете в виду "Руководство по языку SQL", том 1, страницы 8.3 - 8.10? Да? Я не спорю, что там всё понятно про иерархические запросы.

← →
European (2004-04-20 15:23) [10]

Про иерархические запросы в общем

← →
European (2004-04-21 11:11) [11]

Способ, подсказанный
> Vlad © (19.04.04 17:14) [3]
помог.
Однако, как сказано в документации Оракл, условие Start With не может содержать подзапросов. А если отделов несколько и их Id находятся в некоторой таблице, то как подсчитать количество клиентов для каждого отдела в одном запросе?

← →
roottim (2004-04-21 11:17) [12]

опиши конкретнее.. приведи примеры табличек со значениями.. и необходимый тебе результат...

например если считать клиентов конечных отделов то достаточно
select o.orgid, o.orgname, count(1) cnt from customers c, org o where c.orgid = o.orgid group by o.orgid, o.orgname

> European (21.04.04 11:11) [11]

> Start With не может содержать подзапросов

не знаю как на восьмом, а на девятом Оракле вполне проходит конструкция вида
start with idOrg in (select .....)

← →
European (2004-04-21 11:19) [14]

Вот именно что не конечных, а со 2 уровня и до конца (до 4 - 5)

← →
European (2004-04-21 11:22) [15]

> не знаю как на восьмом, а на девятом Оракле вполне проходит
> конструкция вида
> start with idOrg in (select .....)

Я это взял из документация по 9 Ораклу, однако не пробовал. сейчас попробую

2European (21.04.04 11:11) [11]
>Однако, как сказано в документации Оракл, условие Start With не может содержать подзапросов
Странно, у меня 8.1.6 только что такой вопрос сожрал и выполнил
select * from T1
start with id in (select id from T2)
connect by id=parent_id

← →
European (2004-04-21 11:31) [17]

> Однако, как сказано в документации Оракл, условие Start
> With не может содержать подзапросов

Упс! Извините, уважаемые, я читаю плохо:):) Спасибо, такая конструкция действительно работает

Рекурсивный иерархический SQL-запрос Найти похожие ветки