Connect by prior и иерархические запросы в Oracle. Sql иерархические запросы
Запрос на SQL для выборки иерархического списка
Вниз
Запрос на SQL для выборки иерархического списка
SuperChel (2008-06-19 22:19) [0]Приветствую Вас Мастера.Есть простая таблица и для простоты пример: у таблицы два поля id и id_roditel в id храниться уникальное значение, а в id_roditel храниться ссылка на поле id. Как вы уже поняли это связный иерархический список. Хочу выбрать всю ветку потомков, указав какой то конкретный узел. Наведите на мысль как составить такой sql запрос.
MsGuns © (2008-06-19 23:06) [1]Стандартный алгоритм рекурсии - в инете масса примеров
SuperChel (2008-06-20 00:32) [2]> MsGuns © (19.06.08 23:06) [1]> > Стандартный алгоритм рекурсии - в инете масса примеров
В настоящее время я решаю проблему при помощи хранимой функции- т.е. по сути программно, но заинтересовало как это сделать посредством только sql запроса.
Если тебя смущает наличие UDF, никто не мешает весь её текст поместить в SQL компонента.Получить нужное одним SELECT, ИМХО, невозможно.
Fin (2008-06-20 08:31) [4]WITH TempTbl AS (SELECT npp, roditel, 1 AS lv FROM Potrebitel AS A WHERE (npp = 6) UNION ALL SELECT node.npp, node.roditel, ft.lv + 1 AS lv FROM Potrebitel AS node INNER JOIN TempTbl AS ft ON node.roditel = ft.npp AND node.roditel <> node.npp)SELECT npp, roditel, lvFROM TempTbl AS pp
в npp=6 помещаешь номер узла и в результате запроса получаешь всех потомков. Очень удобная штука.
Почитай тут http://www.sqlbooks.ru/readarticle.aspx?part=02&file=sql200509
Johnmen © (2008-06-20 08:56) [5]Не существует решения в рамках стандартных конструкций SQL. И быть не может, ибо "иерархические списки" (т.е. дерево) никакого отношения к реляционной модели не имеют.Но в некоторых диалектах SQL существуют специально введенные искусственные конструкции, специально для этого предназначенные.
Да, мой пример справедлив для MS SQL 2005.хотя в приведённой ссылке упоминается Стандарт ISO SQL:1999 - предусматривающий рекрусивные запросы - может с их помощю можно попробывать.
stas © (2008-06-20 09:35) [7]SuperChel (19.06.08 22:19) Можно без рекурсии, в функции временная таблица + цикл, могу для примера опубликовать свою функцию. Кстати работает быстрее чем WITH на 2005. Но все равно лучше написать триггера, которые будут наполнять/изменять таблицу содержащую нужный набор записей.
Fin (2008-06-20 09:51) [8]Кстати вариант с триггером ИМХО очень перспективный вариант.
Ega23 © (2008-06-20 10:30) [9]> Кстати вариант с триггером ИМХО очень перспективный вариант.
От ситуации зависит. Может быть и перспективным. А может - и нет.
clickmaker © (2008-06-20 10:39) [10]чё-то я не понял: причем тут триггера?
MsGuns © (2008-06-20 10:42) [11]>SuperChel (20.06.08 00:32) [2] >как это сделать посредством только sql запроса.
Никак. Делается две хранимки - одна "складывает" полученных "деток" (входимость в парент непосредственная) в результсет, вторая - выборка "деток", которая вызывается рекурсивно.
>stas © (20.06.08 09:35) [7] >Можно без рекурсии, в функции временная таблица + цикл, могу для примера >опубликовать свою функцию. Кстати работает быстрее чем WITH на 2005. Но все >равно лучше написать триггера, которые будут наполнять/изменять таблицу >содержащую нужный набор записей.
1. Через временную таблицу не быстрее2. Зачем там with ?3. Каким боком там вообще триггеры ?
stas © (2008-06-20 10:52) [12]MsGuns © (20.06.08 10:42) [11]
1. быстрее чем with3. with синтаксис 20053.Тот набор записей, который содержит иеархический список, хранится непосредственно в таблице, которая в свою очередь наполняется триггерами, таким образом при выборке используется таблица, без всяких функций и рекурсий.
Ega23 © (2008-06-20 10:57) [13]> 3.Тот набор записей, который содержит иеархический список,> хранится непосредственно в таблице, которая в свою очередь > наполняется триггерами, таким образом при выборке используется > таблица, без всяких функций и рекурсий.>
Таблица изменяется 3 раза в секунду, а запрос на выборку данных раз в час нужен.Тоже триггеры будешь лепить?А многопользовательский доступ к этой временной таблице? А её содержимое при перезагрузке сервера?
В частном маленьком случае этот вариант ещё может пригодиться, в общем и целом - фтопку.
MsGuns © (2008-06-20 11:05) [14]>stas © (20.06.08 10:52) [12] >2. with синтаксис 2005
Чем with 2005-го отличается от with 2000-го ?
>3.Тот набор записей, который содержит иеархический список, хранится >непосредственно в таблице, которая в свою очередь наполняется триггерами, таким >образом при выборке используется таблица, без всяких функций и рекурсий.
Надо полагать, что таблица постоянная (иначе как в нее встромить триггеры) ? Тогда вопрос на засыпку - как эта таблица будет "обслуживать" несколько одновременных запросов на разузлование ?
Вы придумали велосипед с треугольными колесами и вторым рулем вместо седла. И пытаетесь его "запатентовать" ;)
stud © (2008-06-20 11:05) [15]> Таблица изменяется 3 раза в секунду, а запрос на выборку > данных раз в час нужен.> Тоже триггеры будешь лепить?> А многопользовательский доступ к этой временной таблице?> А её содержимое при перезагрузке сервера?ну тут наверное типа временная таблица с тими триггерами (есть такое в 2005?) и при добавлении записи в нее триггер вытаскивает типа "детей", добавляет их в нее и получается в общем и целом бред, хотя работать будет
Ega23 © (20.06.08 10:57) [13]
что имеете ввиду под маленьким случаем?Я предложил несколько вариантов выбираемый вариант зависит от задачи.Зачастую все происходит наоборот, таблица корректируется не так уж и часто, а выборка используется часто.Пример разузловка оборудования на большом предприятии, поступление нового оборудование происходит раз в день. А ремонты существующего и затраты на ремонты постоянно...А многопользовательский доступ разрулит MSSQL.> А её содержимое при перезагрузке сервера?Вы запутались...1-й вариант наполняем в нутри функции временную таблицу и возвращаем результат2-й в постоянную ведем запись триггерами.
stas © (2008-06-20 11:08) [17]Внимательно читайте:1. Можно без рекурсии, в функции временная таблица + цикл, могу для примера опубликовать свою функцию. Кстати работает быстрее чем WITH на 2005. 2. Но все равно лучше написать триггера, которые будут наполнять/изменять таблицу содержащую нужный набор записей.
1 и 2 не связаны между собой!!!
stud © (2008-06-20 11:08) [18]> 2-й в постоянную ведем запись триггерами?????? и что получится в результатечерез некоторое время она станет копией основного справочника, создавать еще одну "постоянную" таблицу?
MsGuns © (2008-06-20 11:11) [19]В ИБ, где есть suspend (т.е. отсылка клиенту записи сразу после ее выборки без предварительного кэширования во врем.таблицу, которую затем в готовом виде сервер и передает клиенту,- как в мсскл), весь алгоритм можно с успехом реализовать одной-единственной хранимкой, которая вызывает саму себя рекурсивно.
Работает ясно, просто и быстро.
Пример можно подсмотреть в "Мир интербэйз" Вострикова-Ковязина (Часть I, глава ИМХО о хранимках)
stas © (2008-06-20 11:12) [20]она не станет копией справочника. Она станет копией результата отработки рекурсивной ХП
Fin (2008-06-20 11:14) [21]> Ega23 © (20.06.08 10:30) [9]> > Кстати вариант с триггером ИМХО очень перспективный вариант.> От ситуации зависит. Может быть и перспективным. А может > - и нет.Согласен, но я не уточнил что относительно моих задач, где таблица пополняется крайне редко (единичные случаи в месяц), а вот выборка при расчете в основном и "загружает" сервак.
>stud © (20.06.08 11:05) [15] >ну тут наверное типа временная таблица с тими триггерами (есть такое в 2005?) и при >добавлении записи в нее триггер вытаскивает типа "детей", добавляет их в нее и
Вы, извиняюсь, вообще представляете себе, для чего используются триггери и как они работают ?
>получается в общем и целом бред,
вот это уже ближе к истине
>хотя работать будет
Угу, и подвесит нафиг сервер к чертовой матери.
stas © (2008-06-20 11:15) [23]MsGuns © (20.06.08 11:11) [19] в MSSQL в хранимках есть 1 минус, результат не так уж просто объеденить с другой таблицей.
MsGuns © (2008-06-20 11:17) [24]>stas © (20.06.08 11:15) [23] >в MSSQL в хранимках есть 1 минус, результат не так уж просто объеденить с другой >таблицей.
Сами придумали или кто вумный поделился "опытом" ?
stas © (2008-06-20 11:17) [25]MsGuns © (20.06.08 11:15) [22] У меня наоборот вешался, пока не перешли на таблицы.
MsGuns © (20.06.08 11:17) [24] у вас есть простое решение?
Правильный-Вася (2008-06-20 11:25) [27]> наполняется триггерами, таким образом при выборке используется > таблица, без всяких функций и рекурсий.это и есть завуалированная рекурсия, если ты не догадалсятолько рекурсия наполнения, а не чтения
stas © (2008-06-20 11:26) [28]Правильный-Вася (20.06.08 11:25) [27]
Читаем жирным>таким образом при выборке используется > таблица, без всяких функций и рекурсий.
stud © (2008-06-20 11:32) [29]> Вы, извиняюсь, вообще представляете себе, для чего используются > триггери и как они работают ?очень смутно))есть таблица, в нее запросом добавляется запись с ид узла. далее срабатывает триггер на вставку, который ищет "детей" для добавленной записи, вставляет их в эту же таблицу, опять срабатывает (получается аналог рекурсии) или пардон с триггерами есть другой вариант?
> Она станет копией результата отработки рекурсивной ХПэто в случае если данные из нее после получения удаляются.
Fin (2008-06-20 11:48) [30]> stud © (20.06.08 11:32) [29]> > есть таблица,> в нее запросом добавляется запись с ид узла. далее срабатывает > триггер на вставку, который ищет "детей" для добавленной > записи, вставляет их в эту же таблицу, опять срабатывает > (получается аналог рекурсии)
На сколько я понимаю триггер наложен на основную таблицу и перестраивает служебную в которой в упрошенном варианте тригерров нету.
stas © (2008-06-20 11:50) [31]stud © (20.06.08 11:32) [29] Напишу только про Insert при добавлении записи в основную таблицу срабатывает триггер, который рекурсивно либо каким-то другим способом находит всех родителей только на вставленную запись, и заполняет другую таблицу. Таким образом у нас рекурсия работает, только на корректировку таблицы и только по тем записям которые связаны с редактируемой.Это повшает производительность и не тянет много ресурсов при выборке, но снижает при корректировке таблицы.
Ega23 © (2008-06-20 12:01) [32]Я всё равно продолжаю не понимать, нафига нужен триггер и особенно - временная таблица?Если есть такая таблица, которая изменяется крайне редко, но оттуда частые селекты идут - ну и заточи её под селекты изначально. Сделай какой-нибудь AbsoluteLevel и какой-нибудь ChildCount и AllChilsCount (тут можно подумать да в сети почитать).
А временная таблица в данной ситуации - это вообще жесть. В MSSQL их 2 типа - одни с # - на конкретный spid со временем жизни <= времени жизни spid. Второй тип - с двумя ## - в рамках сервера со временем жизни <= времени жизни самого сервера.При разрыве соединения в первом случае и остановке (перезапуске) сервера во втором - ТАБЛИЦЫ БУДУТ ГРОХНУТЫ.
А в первом случае такая временная таблица будет создаваться для КАЖДОГО КЛИЕНТА.
stas © (2008-06-20 12:03) [33]Ega23 © (20.06.08 12:01) [32] Ты ничего непонял...
stas © (2008-06-20 12:06) [34]триггер+временная таблица такого нет!есть триггер+постоянная таблица это 1 вариантесть Multi statement function, которая по своей структуре подразумевает временную таблицу и возвращает ее результат это 2 -й вариант.
Ega23 © (2008-06-20 12:06) [35]> Ega23 © (20.06.08 12:01) [32]> Ты ничего непонял...>
Я понял всё прекрасно. И вообще, за последние 2.5 года 70% времени программирования серверной части уходило на работу с иерархическими структурами под MSSQL.Так что не надо мне тут... :)
stas © (2008-06-20 12:08) [36]> при добавлении записи в основную таблицу срабатывает триггер,> который рекурсивно либо каким-то другим способом находит > всех родителей только на вставленную запись, и заполняет > другую таблицу.так это получается для каждого узла своя таблица????и если нужно получить на клиента только потомков начиная с текущего узла?чето логика пользования такого варианта не совсем понятна.....
MsGuns © (2008-06-20 12:57) [38]Чет его знает, как ты умудряешься повесить сервер..У меня разузлование выполняется на "деревянной" таблице с 5 млн.записей, причем вместо ид-ра используются нативные стринги, а кроме того еще из 5 разных таблиц берутся уточняющие данные (замены, техотход, позаказная расцеховка, наименования и прочий "интерьер"). "Дерево" из 30 000 узлов выгребается за 4 мин. Из 10 000 - за 30 сек. MS SQL 2000
Воистину рыба портится с головы ;)
stas © (2008-06-20 13:07) [39]MsGuns © (20.06.08 12:57) [38] Сколько пользователей юзает одновременно эти деревья?
stud © (20.06.08 12:40) [37] нет.
Anatoly Podgoretsky © (2008-06-20 13:23) [40]> stas (20.06.2008 13:07:39) [39]
А тебе зачем, разве это чтото дает в понимании. Неинтересная это информация.
MsGuns © (2008-06-20 14:39) [41]>stas © (20.06.08 13:07) [39] >Сколько пользователей юзает одновременно эти деревья?
Пользователи не "юзают деревья" по той простой причине, что этих деревьев на сервере нетути. Они строятся на клиенте по датасету, полученному с сервера в рез-те разузловки. После выполнения ХП сервер передает НД клиенту и благополучно о нем "забывает". Поэтому можно вести речь только о взможной конкуренции РАСЧЕТОВ. Однако влияние этой конкуренции малозаметно, т.к. сервер выполняют еще массу другой работы, да и баз данных на сервере не одна и даже не 10.
stas © (2008-06-20 15:07) [42]MsGuns © (20.06.08 14:39) [41] Ну, сдесь можно долго спорить.У меня было построение дерева с помощью функции и есть статичная таблица, которая наполняется триггерами, серверу значительно полегчало.
MsGuns © (2008-06-20 15:50) [43]>stas © (20.06.08 15:07) [42] >Ну, сдесь можно долго спорить.
О чем спорить ? Что изобретенный Вами велосипед с двумя рулями и треугольными колесами лучше обычного ?Сервер "тормозил" не от отсутствия "статичной" таблицы, а из-за кривизны алгоритма, который здесь не обсуждается.Есои Вы полагаете, что наличие триггеров "ускоряет" работу сервера с таблицей, то Вы сильно заблуждаетесь, особенно если эти самые триггеры еще что-то куда-то вставляют.
stas © (2008-06-20 16:00) [44]MsGuns © (20.06.08 15:50) [43] изобретенный Велосипед? вы заблуждаетесь, велосипед изобретаете вы, к тому же вы не ответили на мой вопрос по поводу простого решения объединения результатов хп с другой таблицей.и что вы называете обычным велосипедом?
Ega23 © (2008-06-20 16:04) [45]> к тому же вы не ответили на мой вопрос по поводу простого > решения объединения результатов хп с другой таблицей.
А что такого? В MSSQL только один способ есть :ctreate table #xxx (....)
Insert into #xxx exec c_proc
Select ..... from Table T1 inner join #xxx X on (T... = X....)
stas © (2008-06-20 16:06) [46]Ega23 © (20.06.08 16:04) [45] Ну это же не простой способ. функцию проще объеденить.
MsGuns © (2008-06-20 16:12) [47]>stas © (20.06.08 16:00) [44] >вы заблуждаетесь, велосипед изобретаете вы,
Я уже приводил выше откуда можно взять готовый алгоритм. Если Вам действительно это интересно, потрудитесь ее найти, почитать указанную главу и разобраться.
>к тому же вы не ответили на мой вопрос по поводу простого решения объединения >результатов хп с другой таблицей.
Из фунции можно вызвать ХП и ее результат связать с любым другим запросом, хранимкой или функцией. Напишите нужную Вам функцию на сервере и с клиента будете писать простой запрос Select from функция
>и что вы называете обычным велосипедом?
Стандартный механизм рекурсии, упомянутый в [1], который Вас почему-то вообще не заинтересовал.Вы хотите, чтобы я дал Вам готовый универсальный код ? Я его не дам по двум причинам. Во-первых такого кода не существует из-за особенностей скл-серверов, о чем Вам тут неоднократно сообщалось. А во-вторых, учитесь не брать готовое, а искать в указанных Вам направлениях.
Не сем тему обсуждения считаю исчерпанной
Ega23 © (2008-06-20 16:22) [48]> Ну это же не простой способ. функцию проще объеденить.
В функции динамический SQL низя использовать.
stas © (2008-06-20 16:25) [49]MsGuns © (20.06.08 16:12) [47] 1. Вы сами противоречите себе, я рассказывал что это делается функцией и что у функции есть приимущество перед хранимкой...2. Вы не вызовите хранимку из функции в MS SQL3. Мне ваш код ненужен т.к. у меня есть свой и ненужен мне ваш готовый код у меня их целая куча, и быстрее и правильнее нет я по крайней мере не находил.
stas © (2008-06-20 16:32) [50]Ega23 © (20.06.08 16:22) [48] Ну не нужен он в этой функции.Я вот непонимаю что вы пытаетесь оспоритья предлагаю 2 варианта
1. Это функция без всякой рекурсии (вобще я непредставляю как можно сделать рекурсивную функцию) Которая по перечисленым выше параметрам превосходит хранимку2. Это наполнение постоянной таблицы триггерами.
2-й вариант на выборке довольно шустрый. И с введением именно 2-го варианта у себя в деревьях серверу на много полегчало.А какой вариант выбирать автору это уже его дело.
Ega23 © (2008-06-20 16:37) [51]> (вобще я непредставляю как можно сделать рекурсивную функцию
А что такого?
b z (2008-06-20 16:39) [52]> 2. Это наполнение постоянной таблицы триггерами.Покажите, пож-ста, пример структуры для "дерева", основанный на этом варианте.
stas © (2008-06-20 16:39) [53]Ega23 © (20.06.08 16:37) [51] Результат функции - набор данных, как мне ее вызвать внутри самой себя?
stas © (2008-06-20 16:45) [54]b z (20.06.08 16:39) [52] Структуру таблиц? или привести и триггеры?
Ega23 © (2008-06-20 16:51) [55]> Результат функции - набор данных, как мне ее вызвать внутри > самой себя?>
А что такого?
b z (2008-06-20 16:52) [56]> stas © (20.06.08 16:45) [54] Таблиц, с тригерами надеюсь будет само-собой ... :)
stas © (2008-06-20 16:55) [57]Ega23 © (20.06.08 16:51) [55] Ну, в принципе можно, только это будет тоже самое что я предложил только вместо цикла который в multi statement функции будет рекурсия.
stas © (2008-06-20 17:02) [58]b z (20.06.08 16:52) [56] ТаблицаIDZGLZAP - PARETNTIDNOMZAP - ID
Цель таблицы отобразить всех детей каждого родителя.
Триггер на INSERT основной таблицы:В эту таблицу пакетная вставка данных непредумотрена
SET @NZAP = (SELECT NOMZAP FROM INSERTED )INSERT INTO FSOSTZAKA (GLZAP,NOMZAP)SELECT GLZAP, NOMZAP FROM FSOSTZAKUP (@NZAP) FSOSTZAKUP (@NZAP) - функция в цикле достает всех родителей этой записи
stas © (2008-06-20 17:05) [59]Вот функция которая это все делает, думаю разберетесь.Основная таблица называется SOSTZAKALTER FUNCTION [dbo].[FSOSTZAKUP](@NOMZP as int )RETURNS@TAB TABLE (IDNOMZ INT IDENTITY (1,1) NOT NULL, GLZAP INT, NOMZAP INT, UROVEN INT primary key (IDNOMZ) ) --Создание временной таблицы
AS BEGINDECLARE @MAXID INT, @PMAXID INT, @I INT
INSERT INTO @TAB (GLZAP, NOMZAP, UROVEN) SELECT SOSTZAK.NOMZAP, SOSTZAK.NOZP, 0 FROM SOSTZAK with(nolock) WHERE NOMZAP=@NOMZP --Занесение во временную таблицу записи из SOSTZAK
SET @I=1 --уровеньSET @PMAXID=0--(SELECT MAX(IDNOMZ) FROM @TAB)--1 --предыдущий максимальный IDSET @MAXID=(SELECT MAX(IDNOMZ) FROM @TAB) --максимальный ID
WHILE @PMAXID<>@MAXID AND @I<10 --максимальный уровень (защита от зацикливания)
BEGIN
INSERT INTO @TAB (GLZAP, NOMZAP, UROVEN) SELECT SOSTZAK.GLZAP, SZAK.NOZP, @I FROM @TAB SOSTZAK INNER JOIN SOSTZAK SZAK with(nolock) ON SOSTZAK.NOMZAP=SZAK.NOMZAP WHERE IDNOMZ>@PMAXID --Присоединение таблицы SOSTZAK
SET @I=@I+1SET @PMAXID=@MAXIDSET @MAXID=@@identity
END
RETURNEND
Страницы: 1 2 вся веткаФорум: "Базы";Поиск по всему сайту: www.delphimaster.net;Текущий архив: 2009.02.01;Скачать: [xml.tar.bz2];Наверх
Память: 0.9 MBВремя: 0.079 c
www.delphimaster.net
Connect by prior и иерархические запросы в Oracle « Справочник по Oracle PL/SQL
Иерархические запросы в Oracle обеспечиваются фразой CONNECT BY в операторе SELECT. Эта фраза употребляется в запросе после фразы WHERE и имеет синтаксис, показанный на рис.3.22.
Рисунок 3.22 – Синтаксис фразы CONNECT BY Oracle
Oracle формирует иерархическую выборку, выполняя следующие шаги:
- Oracle выбирает корневую строку (строки) иерархии – ту строку, которая удовлетворяет условию в выражении START WITH.
- Затем выбираются дочерние строки для каждой корневой строки. Каждая дочерняя строка должна удовлетворять условию в фразе CONNECT BY по отношению к одной из корневых строк.
- Выбираются следующие поколения дочерних строк. Сначала выбираются потомки строк, выбранных на шаге 2, потом – их потомки и т.д.
- Oracle всегда выбирает потомков, вычисляя условия CONNECT BY относительно текущей родительской строки.Если запрос содержит фразу WHERE, исключаются все строки, которые не удовлетворяют условию в фразе WHERE. Oracle вычисляет эти условия для каждой строки, а не просто удаляет всех потомков строки, которая не удовлетворяет условию.
Оператор SELECT, выполняющий иерархический запрос, не может содержать соединение.
Выражение START WITH – задает строку/строки, лежащие в корне иерархии. Это выражение определяет условие, которому должны соответствовать корневые строки. Условие может содержать вложенные запросы. Если эта фраза не задана, то все строки таблицы являются корневыми.
CONNECT BY – задает отношение между родительскими и дочерними строками в иерархии. Отношение задается «P-условием», это может быть любое сравнение, но какая-то его часть должна содержать ключевое слово PRIOR, относящееся к родительской строке.
Чтобы найти дочерние строки, Oracle вычисляет PRIOR-выражение для родительской строки, а другое выражение – для каждой строки таблицы. Строки, для которых это выражение дает истину, являются дочерними. CONNECT BY может содержать и другие условия-фильтры. CONNECT BY не может содержать вложенных запросов.
Если CONNECT BY приводит к петле, Oracle возвращает ошибку.
3.5.1.2 Запрос: выбрать фамилии всех прямых начальников сотрудника по фамилии ADAMS.
SELECT employee_id, last_name, manager_id FROM employee CONNECT BY PRIOR manager_id=employee_id START WITH last_name= 'ADAMS'Обработка иерархии обеспечивается выражением CONNECT BY, которое выполняет рекурсивную выборку строк: условие, по которому выбирается следующая строка, определяется значениями, выбранными в составе текущей строки. В нашем случае следующей выбирается строка, в которой значение manager_id равно значению employee_id в только что выбранной строке. Выражение START WITH определяет условие выборки первой строки.
3.5.1.3 Запрос: вывести структуру подчиненности в фирме.
SELECT level, employee_id, last_name, manager_id FROM employee CONNECT BY PRIOR employee_id=manager_id START WITH last_name= (SELECT last_name FROM employee, job WHERE employee.job_id=job.job_id AND FUNCTION ='PRESIDENT' )В решении используется условие CONNECT BY, инвертированное по сравнению с предыдущей задачей. В этом случае следующей выбирается строка, в которой значение employee_id равно значению manager_id в только что выбранной строке, что обеспечивает движение от корня дерева вниз. Начальной строкой является та, которая содержит код должности, соответствующий функции PRESIDENT. Выборки Oracle, использующие иерархические свойства запросов, могут использовать псевдостолбец level. Этот псевдостолбец имеет значение 1 для узла дерева, находящегося в корне, 2 – для узлов, являющихся непосредственными потомками корневого, и т.д.
Источник: http://khpi-iip.mipk.kharkiv.edu/library/extent/dbms/sql/35.htmlОффдоки: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm
Запись опубликована 02.01.2011 в 3:21 пп и размещена в рубрике Oracle PL/SQL. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.
plsqlbook.ru
sql - CONNECT BY или иерархические запросы в СУБД, отличные от Oracle
Oracle поставляется с очень удобной функцией. Вы можете создавать иерархические запросы (рекурсивное поведение), используя следующее предложение:
CONNECT BY [NOCYCLE] {condition [AND condition...]} [START WITH condition]Как описано здесь:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries003.htm
Мне интересно, существуют ли какие-либо другие RDBMS, поддерживающие эквивалентный или похожий синтаксис? Или может быть рекурсивное поведение, подобное этому, в общем случае моделироваться с использованием обычного SQL?
Хорошим примером, который я хотел бы смоделировать, является это (взято из документации Oracle):
SELECT LPAD(' ', 2 * (LEVEL-1)) || last_name org_chart, employee_id, manager_id, job_id FROM employees START WITH job_id = 'AD_VP' CONNECT BY PRIOR employee_id = manager_id;Результат:
ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- Kochhar 101 100 AD_VP Greenberg 108 101 FI_MGR Faviet 109 108 FI_ACCOUNT Chen 110 108 FI_ACCOUNT Sciarra 111 108 FI_ACCOUNT Urman 112 108 FI_ACCOUNT Popp 113 108 FI_ACCOUNT Whalen 200 101 AD_ASST Mavris 203 101 HR_REP Baer 204 101 PR_REP Higgins 205 101 AC_MGR Gietz 206 205 AC_ACCOUNT De Haan 102 100 AD_VP Hunold 103 102 IT_PROG Ernst 104 103 IT_PROG Austin 105 103 IT_PROG Pataballa 106 103 IT_PROG Lorentz 107 103 IT_PROGПсевдо-столбец LEVEL и достигнутый с ним отступ не так важны для меня
источник поделитьсяqaru.site
sql - Запрос иерархических данных в SQL
Как вы пишете рекурсивный SQL-запрос, который показывает разные уровни иерархической связи как отдельные результаты?
У меня есть таблица в моей базе данных (Container), которая имеет иерархические отношения с самим собой (любой контейнер может иметь много внутренних контейнеров). Я пытаюсь создать представление, которое показывает каждый контейнер со всеми его внешними участниками (а не только его непосредственным внешним), а также количеством уровней внутри внутреннего контейнера. Например:
Если в таблице "Контейнер" были записаны следующие записи:
- Id: 1 externalId: null
- Id: 2 externalId: 1
- Id: 3 externalId: 1
- Id: 4 externalId: 2
- Id: 5 наружный: 4
Я бы хотел, чтобы представление показывало:
- innerId: 2 externalId: 1 lvl: 1
- innerId: 3 externalId: 1 lvl: 1
- внутреннийId: 4 внешнийId: 1 lvl: 2
- внутреннийId: 4 внешнийId: 2 lvl: 1
- innerId: 5 externalId: 1 lvl: 3
- внутреннийId: 5 внешнийId: 2 lvl: 2
- внутреннийId: 5 внешнийId: 4 lvl: 4
НЕ
- Id: 2 Путь: 1
- Id: 3 Путь: 1
- Id: 4 Путь: 1,2
- Id: 5 Путь: 1,2,4
В принципе, я хочу узнать, какие внутренние контейнеры находятся внутри конкретного контейнера, выполнив: select innerId from v_MyView where outerId = @outer_id и выяснить, в каких контейнерах находится конкретный контейнер, выполните: select outerId from v_MyView where innerId = @inner_id
Обновить
Я использую SQL Server 2012, и я довольно familer с рекурсивными CTE. Проблема, с которой я сталкиваюсь, заключается не в том, что я не знаю, как писать рекурсивный CTE, а скорее из того, что мне нужно, чтобы результат был в нескольких строках, а не в конкатенированном поле в представлении.
Это то, что у меня уже есть:
with MYCTE as ( select Id, Cast(null as varchar(max)) as cntr_path, 0 as lvl from Container where Container.outerId is null union all select Container.Id, IsNull(cntr_path + ',','') + '[' + cast(Container.outerId as varchar(max)) + ']', lvl + 1 from Container join MYCTE on Container.outerId = MYCTE.Id ) select * from MYCTE where cntr_path is not nullно это создает только одну строку на внутренний контейнер. То, что я хочу, - это одна строка на внутренний контейнер на внешний контейнер
Я предполагаю, что лучший способ описать представление, которое я хочу, - это "таблица ссылок", которая связывает каждый внешний контейнер с каждым из них - как с "внешним", так и с "внутренним", действующим как внешние ключи (оба указывающие на Id в контейнере).
qaru.site
sql - Иерархический запрос
Мне нужно получить все подчиненные, когда я выбираю супервизора. Я передаю employee_id из внешнего интерфейса, тогда он должен получить данные, которые все подпадают под него.
У меня есть структура таблиц для выравнивания и сотрудника ниже.
таблицы: Выравнивание:
ALIGNMENT_ID NOT NULL NUMBER(16) TEAM_ID NOT NULL NUMBER(16) EMPLOYEE_ID NUMBER(16) ALIGNMENT_NAME NOT NULL VARCHAR2(200) EXTERNAL_ID_1 VARCHAR2(200) STATUS NOT NULL VARCHAR2(4) STATUS_CHANGE_DATE NOT NULL DATE MANAGER_ALIGNMENT_ID NUMBER(16) TEAM_ADMINISTRATOR VARCHAR2(4) REPORT_LEVEL VARCHAR2(4) ROLE VARCHAR2(4) POD_ALIGNMENT_ID NUMBER(16)сотрудник:
EMPLOYEE_ID NOT NULL NUMBER(16) EXTERNAL_ID_1 VARCHAR2(200) EXTERNAL_ID_2 VARCHAR2(200) JOB_PROFILE_TYPE VARCHAR2(4) FIRST_NAME NOT NULL VARCHAR2(200) MIDDLE_NAME VARCHAR2(200) LAST_NAME NOT NULL VARCHAR2(200) SECOND_LAST_NAME VARCHAR2(200) DISPLAY_NAME VARCHAR2(200) BIRTH_DATE DATE STATUS NOT NULL VARCHAR2(4) STATUS_CHANGE_DATE NOT NULL DATE STATUS_REASON VARCHAR2(4) SAMPLING_STATUS VARCHAR2(4) MACHINE_NODE_ID NUMBER(9) LOGIN_ID VARCHAR2(200) TITLE VARCHAR2(4)данные для выравнивания:
17432000000411 7888500000120271 17432000000427 india_ho india_ho ACTV 28-06-2013 06:44:53 0 VPOS 1006387676 7888500000120271 1006381821 EST REGION EST REGION ACTV 28-05-2013 15:41:56 17432000001417 RM 1006387677 7888500000120271 60000001963869 E-AREA1 E-AREA1 ACTV 28-05-2013 15:41:56 1006387676 DM 1006387678 7888500000120271 60000001963939 E-AREA2 E-AREA2 ACTV 28-05-2013 15:41:56 1006387676 DM 1006387679 7888500000120271 60000001963930 E-AREA3 E-AREA3 ACTV 28-05-2013 15:41:56 1006387676 DM 1006387680 7888500000120271 60000001963946 E-AREA4 E-AREA4 ACTV 28-05-2013 15:41:56 1006387676 DM 1006387681 7888500000120271 60000001963733 E-AREA6 E-AREA6 ACTV 28-05-2013 15:41:56 1006387676 DM 1006387682 1000000338771 60000001963734 E-AS-GUW-A07 E-AS-GUW-A07 ACTV 28-05-2013 16:17:30 1006387680 REP 1006387683 1000000338771 60000001963711 E-AS-GUW-A08 E-AS-GUW-A08 ACTV 28-05-2013 16:17:30 1006387680 REP 1006387684 1000000338772 60000001963729 E-AS-GUW-B07 E-AS-GUW-B07 ACTV 28-05-2013 16:17:30 1006387680 REP 1006387685 1000000338772 60000001963786 E-AS-GUW-B08 E-AS-GUW-B08 ACTV 28-05-2013 16:17:30 1006387680 REP 1006387686 1000000338771 18910000251233 E-CT-RAIP-A11 E-CT-RAIP-A11 ACTV 28-05-2013 16:17:30 1006387679 REP 1006387687 1000000338772 18910000194383 E-CT-RAIP-B11 E-CT-RAIP-B11 ACTV 28-05-2013 16:17:30 1006387679 REP 1006387688 1000000338771 E-MP-JABA-A09 E-MP-JABA-A09 INAC 19-01-2015 11:31:33 1006387681 REP 1006387689 1000000338773 60000001963891 E-MP-JABA-B09 E-MP-JABA-B09 ACTV 28-05-2013 16:17:30 1006034504 REP 1006387690 1000000338771 60000001963805 E-OR-BHUB-A05 E-OR-BHUB-A05 ACTV 28-05-2013 16:17:30 1006387678 REP 1006387691 1000000338772 60000001963873 E-OR-BHUB-B05 E-OR-BHUB-B05 ACTV 28-05-2013 16:17:30 1006387678 REPВ таблице выравнивания мы имеем роль, как «VPOS'- -1, 'RM' - 2, 'DM' - 3, 'REP' - 4. Я отдал приказ как p до дна. VPOS является главным лицом (супервизор).
Если я выбираю VPOS, тогда все данные должны извлекаться из запроса. Если я выберу RM, тогда DM и REP должны быть отображены, и если я выберу DM, то REP должен быть отображен.
stackoverrun.com
