VPF::Получить всех "потомков" строки таблицы

kami

Дата 24.7.2012, 19:54 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1806
Регистрация: 25.8.2007
Где: Санкт-Петербург

Репутация: 3
Всего: 72

Доброго времени суток, уважаемые!
Столкнулся со следующей проблемой:
имею таблицу (MS SQL Express 2008 SP3) вида:

Код


  AOGUID uniqueidentifier NOT NULL,  -- GUID самого объекта
  PARENTGUID uniqueidentifier NULL,  -- GUID "родителя" объекта
  OFFNAME nvarchar(120) NULL         -- имя объекта
  LEVEL int NOT NULL                  -- уровень вложенности объекта (1..9)

Если нужно больше конкретики, то каждая строка определяет элемент почтового адреса (выборка из БД ФИАС): область/город...

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

Обратную задачу (получение всех предков конкретной записи) я с горем пополам решил, наверное далеко не оптимально, а вот с этой...

superVad

Дата 24.7.2012, 20:39 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 735
Регистрация: 6.4.2006
Где: Черкассы, Украина

Репутация: 3
Всего: 15

Цитата(kami @ 24.7.2012, 18:54

)

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

Три запроса через юнион напиши.
Если AOGUID 3 например:

Код


select *
from table t1
  inner join table t2 on t2.AOGUID = t1.PARENTGUID and t2.AOGUID = 3

UNION

select *
from table t1
  inner join table t2 on
    inner join table t3 on t3.AOGUID = t2.PARENTGUID and t3.AOGUID = 3
  t2.AOGUID = t1.PARENTGUID

UNION

select *
from table t1
  inner join table t2 on
    inner join table t3 on
      inner join table t4 on t4.AOGUID = t2.PARENTGUID and t4.AOGUID = 3
    t3.AOGUID = t2.PARENTGUID
  t2.AOGUID = t1.PARENTGUID

Вероятно с ошибками написал. Но возможно как то так. Ну и table надо на свое название таблицы заменить. И вместо звездочки можно список полей, во всех запросах одинаковый.

Это сообщение отредактировал(а) superVad - 24.7.2012, 20:41

kami

Дата 25.7.2012, 22:33 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1806
Регистрация: 25.8.2007
Где: Санкт-Петербург

Репутация: 3
Всего: 72

superVad, спасибо, натолкнул на идею.
Моя вина - некорректно описал задачу, которую нужно решить. Прошу прощения за "многабукав", но изложить четко и кратко не получается :(

в таблице содержатся все адресные элементы: начиная от республики/области/города федерального значения (уровень 1) и заканчивая улицами/проулками/просеками (уровень 7, 8, 9). Четкой вложенности нет - уровень 7..9 может быть подчинен напрямую уровню 1, 2...

Что хотелось бы (на конкретном примере):
пользователю нужно ввести улицу Ленина поселка Парголово города Санкт-Петербург. Но он забыл/не знал, что ул.Ленина относится к п.Парголово и ввел в поле "название города" Санкт-Петербург, а там тоже есть такая улица. Естественно, что эти улицы разные.

Соответственно, для обеспечения правильности выбора программа должна выдать дополнительные варианты, с указанием к какому именно адресному объекту относится каждый. В данном случае варианты выбора должны выглядеть как-то так:

1. ул. Ленина
2. ул. Ленина (п. Парголово).

Следовательно, в итоговой выборке из БД должна содержаться не только информация о улице (уровень 7..9), но и о "вышестоящих" объектах, общим предком которых (не обязательно родителем) является введенный город.

Что получилось у меня (упрощенно. @StartStr - начальные символы названия улицы, @ParentGUID - GUID введенного города):

Код


SELECT
    l7fields,    l8fields,    l9fields
FROM
    (
    SELECT
        l7fields, l8fields ,l9fields
    FROM
        (
        SELECT
            l7fields
        FROM
            table l7 with(nolock)
        WHERE
            (l7.PARENTGUID = @ParentGUID) AND (l7.AOLEVEL >6)
        ) level7 
        LEFT JOIN
            table l8 with(nolock)
        ON level7.l7AOGUID = l8.PARENTGUID
    )level8
    LEFT JOIN
        table l9 with(nolock)
    ON level8.l8AOGUID = l9.PARENTGUID
WHERE
    ((l7OFFNAME LIKE @StartStr) OR (l8OFFNAME LIKE @StartStr) OR (a9.OFFNAME LIKE @StartStr))

Проблемы:
1. поиск ведется только по уровням 7..9, таким образом пользователь не имеет возможности ошибиться в названии города.
2. действует долго, первый запрос - около 5-7 секунд, остальные - около 1 секунды (индексы по полям OFFNAME, AOGUID и PARENTGUID).

kami

Дата 26.7.2012, 22:13 (ссылка)

(голосов:1)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1806
Регистрация: 25.8.2007
Где: Санкт-Петербург

Репутация: 3
Всего: 72

Дамы и господа, задача решена. Запрос выполняется за 0 секунд (1 378 527 записей).
Всем спасибо за внимание, superVad - персонально за идею.

БОльшую роль сыграла расстановка индексов по рекомендациям SSMS, но и запросы пришлось переделать. Удивительно, но добавление во вложенный запрос одного лишнего условия дало прирост скорости в несколько порядков.

Если кому вдруг придется работать с базой ФИАС (по структуре она лучше КЛАДР-а), выкладываю получившиеся у меня запросы, вдруг пригодятся. БД была сконвертирована из dbf (OEM-кодировка) в SQLExpress своей программкой (импорт с использованием SSMS не увенчался успехом). Структура таблиц ФИАС и наименования полей при конвертировании не менялись, только убраны лишние. И то - с учетом индексов еле уложился в отведенные для Express версии 4Гб на базу.

Присоединённый файл ( Кол-во скачиваний: 114 )

FIASQueries.sql 5,39 Kb

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Delphi: Базы данных и репортинг \| Следующая тема »