Люди, поможите с SQL-запросом

[alibek]

Привет, народ
Помогите составить SQL запрос.
Есть: таблица с иерархическим списком. Есть поля ID, NAME, PARENT; PARENT является ключом на ID родительского элемента, если PARENT=0, то элемент является элементом верхнего уровня (корневым).
Нужно: получить иерархический отсортированный список.

Кодом я бы делал это так:
1. Сделать выборку SELECT * FROM ... WHERE PARENT=0 ORDER BY 1
2. Пройтись по списку и для каждого элемента сделать SELECT * FROM ... WHERE PARENT=[ParentID] ORDER BY 1.
3. Повторить п.2 рекурсивно для каждого загруженного элемента.

Но это надо сделать одним SQL-запросом и что-то никак не могу его придумать. Скорее всего придется вводить вспомогательные поля (например, LEVEL, который будет указывать на глубину вложенности элемента, при LEVEL=0 элемент является корневым).

Заранее спасибо.

[skiperski]

Не совсем понятно, что в итоге должно получиться? Список отсортированный по ???

Но в любом случае всё дерево одним запросом не вычитать. Можно только сколько-то, заранее зная сколько, уровней. (Красиво получилось )

Вот SQL-запрос для выборки трёх уровней

Код: Выделить всё

SELECT c0.kmCatId AS Parent0, c0.kmCatId AS Parent1, c0.kmCatId, 0 AS [Level]
FROM kmCategories c0
WHERE c0.kmParent = 0

UNION

SELECT c1.kmParent AS Parent0, c1.kmCatId AS Parent1, c1.kmCatId, 1 AS [Level]
FROM kmCategories c0
   INNER JOIN kmCategories c1
   ON c1.kmParent = c0.kmCatId
WHERE c0.kmParent = 0

UNION

SELECT c2.kmParent AS Parent0, c2.kmParent AS Parent1, c2.kmCatId, 2 AS [Level]
FROM kmCategories c0
   INNER JOIN kmCategories c1
      INNER JOIN kmCategories c2
      ON c2.kmParent = c1.kmCatId
   ON c1.kmParent = c0.kmCatId
WHERE c0.kmParent = 0


ORDER BY Parent0, Parent1, [Level], kmCatId

Вроде бы правильно получилось.
Тестировал на своей базе, потому названия таблиц и полей надо переправить.

[alibek]

Должен получиться сортированный иерархический список. Т.е. элементы верхнего уровня идут в алфавитном порядке, если у какого-либо элемента есть подчиненные элементы, они идут после него (тоже в алфавитном порядке). Примерно то же, что в TreeView с .Sorted=True для каждого нода.

Дело в том, что однажды я это как-то сделал, делал кому-то программку небольшую, там встала подобная задача и я ее как-то решил, чуть ли не случайно. Но исходников я естественно не сохранял (прога была пустяковая), а теперь не помню, как я сделал. Точно помн, что вводил поля LEVEL (их значения вычисляла функция, она рекурсией добиралась от корня к указанному элементу, число шагов рекурсии и было LEVEL). Помоему еще вводил поле ROOT, которое было ключом на самый верхний (корневой) элемент.
Вообщем, не помню как, но как-то я это сделал, проклятый склероз

[skiperski]

Задача решабельная, но не при таких условиях. Необходимо/ы дополнительное/ые поле/я и программа предобработки.

У нас это реализовано введением полей Position и Level. После добавления/удаления/изменения специально обученная программка бегает по дереву и перенумеровывает эти поля. Выборка из такой структуры уже банальна. Проблема только в асинхронном запуске программы, но у нас свой сервер на котором стартуется сервис который и занимается подобными вещами.

А ещё когда-то пытался делать форум и при наличии отсутствия возможности асинхронной обработки пришлось выдумывать другие методы. Вот чего навыдумывалось:

1. Т.к. нет возможности запускать сервисы, то предобработку необходимо делать при добавлении/удалении записи.

2. Исходя из п.1 обработка должна быть простой и быстрой. Скорость и ресурсоёмкость - две противоположности. Но, т.к. нужна скорость, жертвуем ресурсами. Создаём текстовое поле безумной длины. Максимальная разрешённая длина поля в Access 255 символов.

3. Предположим, что структура иерархическая с неопределённым кол-вом нитей и неопределённой вложенности. Теперь попытаемся вычислить на какого размера структуру ключ мы можем реально сохранить в одном текстовом поле на 255 символов. Предположим, что кол-во кол-во эл-ов в подноде ограничено, например, 1000, что можно представить тремя десятичными символами от 000 до 999. Тогда 255 / 3 = 85 - глубина вложений. По-моему, этого более чем достаточно, учитывая что я пишу форум. Но ежели необходимы бОльшие значения, то можно воспользоваться системой счисления с основанием более 10, например шестнадцатиричной, или кардинально задействовать все печатные символы латинского алфавита и цифры (10 + 26 * 2 = 62).

4. Построение ключа. В корневые ветки пишем только три символа от 000 до 999. Счётчик нарасщиваем по мере добавления ветвей. Т.к. (все помнят?) я пишу форум, то сортировка по алфавиту не нужна, что облегчает задачу.

Дочерние ключи строятся также как и корневые, но с добавлением в начало родительского ключа.

Пример:
000 - первая ветка
001 - вторая ветка
001000 - первая подветка во второй ветке
002 - третья ветвь
000001 - первая подветка в первой ветке
001001 - вторая подветка во второй ветке
001001001 - первая подветка второй подветки во второй ветке

и т.д.

Получилось оченно даже неплохо:
- ключи добавляются быстро и легко;
- сортировка работает;
- запрос на выборку прост до безобразия;
- уровень вычисляется как Len(Key) / 3;
- не нужна доп.программа для пересортировки.

[alibek]

Спасибо, skiperski, ты натолкнул на одну идею. А именно select * from ... order by FullName([ID]), здесь функция FullName возвращает полный путь к элементу (т.е. вида NAME\NAME\NAME).
Завтра погоняю это дело на рабочем объеме данных (200-300 записей), если скорость сойдет, то все окей

[ta_gena]

Запрос:
"SHAPE {SELECT * FROM Level1 ORDER BY ..} AS Level1 " _ &
"APPEND ({SELECT * FROM Level2 ORDER BY ..} AS Level2 RELATE Parent1 TO Parent2) AS Leve1"
Создаст рекордсет с именем Level1, у которого последнее поле будет с именем Level2 и типом adChapter. Т.е. будет иметь поля
ID, Name, Parent, Level2.
Level2 в свою очередь является рекордсетом c полями
ID, Name, Parent

Соответственно можно составить запрос:
"SHAPE {SELECT * FROM Level1 ORDER BY ..} AS Level1 " _ &
"APPEND ((SHAPE {SELECT * FROM Level2 ORDER BY ..} AS Level2 APPEND {SELECT * FROM Level3 ORDER BY ..} AS Level3 RELATE Parent2 TO Parent3) AS Level2 RELATE Parent1 TO Parent2) AS Leve1"

Создаст рекордсет с именем Level1, поля
ID, Name, Parent, Level2.
Level2 в свою очередь является рекордсетом c полями
ID, Name, Parent, Level3
Level3 в свою очередь является рекордсетом c полями
ID, Name, Parent

И т.д. до любой глубины вложения.
При открытии соединения нужно установить:
Provider=MSShape.1
Data Provider=Microsoft.Jet.OLEDB.4.0