Главная страница
    Top.Mail.Ru    Яндекс.Метрика
Форум: "Базы";
Текущий архив: 2009.02.01;
Скачать: [xml.tar.bz2];

Вниз

Запрос на 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 запроса.


 
ЮЮ ©   (2008-06-20 04:30) [3]

Если тебя смущает наличие 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, lv
FROM         TempTbl AS pp


в npp=6 помещаешь номер узла и в результате запроса получаешь всех потомков. Очень удобная штука.

Почитай тут http://www.sqlbooks.ru/readarticle.aspx?part=02&file=sql200509


 
Johnmen ©   (2008-06-20 08:56) [5]

Не существует решения в рамках стандартных конструкций SQL. И быть не может, ибо "иерархические списки" (т.е. дерево) никакого отношения к реляционной модели не имеют.
Но в некоторых диалектах SQL существуют специально введенные искусственные конструкции, специально для этого предназначенные.


 
Fin   (2008-06-20 09:10) [6]

Да, мой пример справедлив для 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. быстрее чем with
2. with синтаксис 2005
3.Тот набор записей, который содержит иеархический список, хранится непосредственно в таблице, которая в свою очередь наполняется триггерами, таким образом при выборке используется таблица, без всяких функций и рекурсий.


 
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?) и при добавлении записи в нее триггер вытаскивает типа "детей", добавляет их в нее и получается в общем и целом бред, хотя работать будет


 
stas ©   (2008-06-20 11:06) [16]

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]
> > Кстати вариант с триггером ИМХО очень перспективный вариант.
> От ситуации зависит. Может быть и перспективным. А может
> - и нет.

Согласен, но я не уточнил что относительно моих задач, где таблица пополняется крайне редко (единичные случаи в месяц), а вот выборка при расчете в основном и "загружает" сервак.


 
MsGuns ©   (2008-06-20 11:15) [22]

>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]
У меня наоборот вешался, пока не перешли на таблицы.


 
stas ©   (2008-06-20 11:21) [26]

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]

аналагично :)


 
stud ©   (2008-06-20 12:40) [37]


> при добавлении записи в основную таблицу срабатывает триггер,
>  который рекурсивно либо каким-то другим способом находит
> всех родителей только на вставленную запись, и заполняет
> другую таблицу.

так это получается для каждого узла своя таблица????
и если нужно получить на клиента только потомков начиная с текущего узла?
чето логика пользования такого варианта не совсем понятна.....


 
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]

А тебе зачем, разве это чтото дает в понимании. Неинтересная это информация.



Страницы: 1 2 вся ветка

Форум: "Базы";
Текущий архив: 2009.02.01;
Скачать: [xml.tar.bz2];

Наверх




Память: 0.57 MB
Время: 0.007 c
2-1229498873
programmer90
2008-12-17 10:27
2009.02.01
Деревья


15-1227739103
DillerXX
2008-11-27 01:38
2009.02.01
Вопрос к пользовтелям googlemail (gmail)


15-1228708456
Mozart
2008-12-08 06:54
2009.02.01
Биллинг...


3-1214028304
lod
2008-06-21 10:05
2009.02.01
Ошибка при открытии таблицы


2-1229421066
D12
2008-12-16 12:51
2009.02.01
Tag





Afrikaans Albanian Arabic Armenian Azerbaijani Basque Belarusian Bulgarian Catalan Chinese (Simplified) Chinese (Traditional) Croatian Czech Danish Dutch English Estonian Filipino Finnish French
Galician Georgian German Greek Haitian Creole Hebrew Hindi Hungarian Icelandic Indonesian Irish Italian Japanese Korean Latvian Lithuanian Macedonian Malay Maltese Norwegian
Persian Polish Portuguese Romanian Russian Serbian Slovak Slovenian Spanish Swahili Swedish Thai Turkish Ukrainian Urdu Vietnamese Welsh Yiddish Bengali Bosnian
Cebuano Esperanto Gujarati Hausa Hmong Igbo Javanese Kannada Khmer Lao Latin Maori Marathi Mongolian Nepali Punjabi Somali Tamil Telugu Yoruba
Zulu
Английский Французский Немецкий Итальянский Португальский Русский Испанский