Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Web программирование > SQL, базы данных
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 10.06.2019, 15:19   #1
polin11
Форумчанин
 
Регистрация: 07.06.2015
Сообщений: 164
По умолчанию Update при помощи рекурсивного запроса

Использую postgresql. Есть иерархическая таблица, поле с иерархией parent_id, нужно заполнить level - уровень вложенности, hier - строка с названиями родителями (исключая корень таблицы), root - значения корня иерархии (если у предка в поле new_root = true, то у потомков в поле root должен быть id этого предка).
Пример можно посмотреть https://www.db-fiddle.com/f/ezdc4n5ivhHrHk6rDJgZUo/0

Код:
CREATE TABLE geo (
    id int not null primary key, 
    parent_id int references geo(id),  
    name varchar(1000),
	 level int,
	 hier varchar(1000),
    root int,
    new_root bool	 
);

INSERT INTO geo 
(id, parent_id, name, level, hier,root, new_root) 
VALUES 
(1, null, 'Планета Земля', null, null,null, null),
(2, 1, 'Континент Евразия', null, null,null, null),
(3, 1, 'Континент Северная Америка', null, null,null, null),
(4, 2, 'Европа', null, null,null, null),
(5, 4, 'Россия', null, null,null, true),
(6, 4, 'Германия', null, null,null, null),
(7, 5, 'Москва', null, null,null, null),
(8, 5, 'Санкт-Петербург', null, null,null, null),
(9, 6, 'Берлин', null, null,null, null);
Написал запрос:
Код:
WITH RECURSIVE r AS (
   SELECT id, parent_id, name, 2 AS level, null AS hier, 1 as root
   FROM geo
   WHERE parent_id = 1

   UNION ALL

   SELECT geo.id, geo.parent_id, geo.name, r.level + 1 AS level,  case when r.hier is NULL then (select name from geo tmp where tmp.id=geo.parent_id limit 1) else  r.hier::text || '#' || (select name from geo tmp where tmp.id=geo.parent_id limit 1) end AS hier,
CASE WHEN (SELECT new_root FROM geo tmp WHERE tmp.id=geo.parent_id LIMIT 1) IS true 
   THEN (SELECT id FROM geo tmp WHERE tmp.id=geo.parent_id LIMIT 1)
   ELSE r.root END AS root
   FROM geo inner JOIN r ON (geo.parent_id = r.id)
),
T AS(
select id,  parent_id, name, 1 AS level, null::text AS hier, id as root
from geo
where id = 1
union
SELECT * FROM r
)
Update geo
set(level, hier, root) = (T.level, T.hier, T.root)
from T
where geo.id=T.id;
select *
from geo
Посоветуйте, как можно улучшить запрос???
polin11 вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Прошу помощи в составлении запроса Император97 SQL, базы данных 1 15.02.2018 22:58
Создание запроса UPDATE, использующего переменные DavidRealy SQL, базы данных 3 05.04.2015 23:47
прошу помощи с составлением запроса lump SQL, базы данных 11 18.09.2014 10:13
Вход в режим запроса помощи в формах, изменяющих размеры (подробно) Peter Burdeh C# (си шарп) 1 08.08.2014 11:43
Ошибка при выполнении запроса на UPDATE semirek БД в Delphi 9 16.01.2013 14:38