Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Рекурсия  [new]
*Ann*
Member

Откуда:
Сообщений: 57
Добрый день.
Задачка такая:
Есть таблица адресов с полями id, adress, formalname, level, parent_id. Parent_id ссылается на id из этой же таблицы.
id adress formalname level parent_id
1 630123, Новосибирская обл, Новосибирск г, Петухова ул Петухова ул 33 2
2 630123, Новосибирская обл, Новосибирск г Новосибирск г 22 3
3 630123, Новосибирская обл Новосибирская обл 11 null

В итоге должна получиться таблица:
id adress Index 11 22 33
1 630123, Новосибирская обл, Новосибирск г, Петухова ул 630123 Новосбирская обл Новосбирск г Петухова ул

Индекса в поле adress может и не быть.
Получается нужно разбить адрес по уровням level, ссылаясь на родителя.

Тут простым запросом не обойдешься, наверное, нужно писать какую-то процедуру, чего я пока не делала. Подскажите, пожалуйста, в каком направлении нужно действовать, что почитать, с чего начать.
25 дек 18, 17:02    [21773146]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
*Ann*
Member

Откуда:
Сообщений: 57
в первом сообщении не понятно как по столбцам разбивается таблица, немного исправила.
Есть таблица адресов с полями id, adress, formalname, level, parent_id. Parent_id ссылается на id из этой же таблицы.
id | adress | formalname | level | parent_id
1 | 630123, Новосибирская обл, Новосибирск г, Петухова ул | Петухова ул | 33 | 2
2 | 630123, Новосибирская обл, Новосибирск г | Новосибирск г | 22 | 3
3 | 630123, Новосибирская обл | Новосибирская обл | 11 | null

В итоге должна получиться таблица:
id | adress | Index | 11 | 22 | 33
1 | 630123, Новосибирская обл, Новосибирск г, Петухова ул | 630123 | Новосбирская обл | Новосбирск г | Петухова ул
25 дек 18, 17:10    [21773151]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1171
*Ann*,

Для начала почитайте про рекурсивный CTE. Глядишь, и процедуру писать не понадобиться...
25 дек 18, 20:21    [21773329]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
*Ann*
Member

Откуда:
Сообщений: 57
Почитала про рекурсивные cte, во всех статьях примеры с выводом дерева в столбик. А мне надо строкой, при этом каждый элемент поместить с столбец в зависимости от уровня level. При этом количество уровней может быть разное. Может быть например 1,2 3 уровень, а может быть 1,3,4 уровень, тогда столбец со вторым уровнем должен быть пустым. Есть какие-то примеры с выводом дерева в строку?
28 дек 18, 05:31    [21775387]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1171
*Ann*,

Возможно, вам нужно такое...

Если же задача всё таки стоит в отдельные столбцы, а не просто одной строкой - то тут прямая дорога к динамическому SQL. А в вашем конкретном случае - сначала вертикальный сбор дерева посредством рекурсивного СТЕ, затем его разворот в строку при помощи динамического PIVOT-а
28 дек 18, 06:54    [21775395]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
PizzaPizza
Member

Откуда:
Сообщений: 183
*Ann*,

если я правильно понял

вы хотите из этого

id adress formalname levelparent_id
1 630123 Новосибирская обл. Новосибирск г. Петухова ул Петухова ул 33 2
2 630123 Новосибирская обл. Новосибирск г Новосибирск г 22 3
3 630123 Новосибирская обл Новосибирская обл 11 null


собирать это

id adress index formalname1 formalname2 formalnameN
1 630123 Новосибирская обл. Новосибирск г. Петухова ул 630123 Новосбирская обл Новосбирск г Петухова ул


хотя полный адрес у вас есть в первой же строке в поле adress, части адреса в полях formalname, и у вас нет нигде индекса как отдельный атрибут

Мне кажется тут нужны некоторые пререквизиты. Вам надо определиться с количеством уровней результирующей таблицы. SQL не может выдать вам строки с произвольным количеством атрибутов, поэтому вам надо понять сколько уровней разбиения адреса у вас есть в базе. Если это всегда четыре (индекс, область/регион, город, улица) то можно будет сделать обычный запрос. Если это более сложное динамическое деление, то вам надо понять по каким признакам вы будете размещать части вашего адреса, иначе у вас в разные колонки пойдут разные данные:

id adress index formalname1 formalname2 formalnameN
1 630123 Новосибирская обл. Новосибирск г. Петухова ул 630123 Новосбирская обл Новосбирск г Петухова ул
2 Московская обл. Москва г. Ленина ул Московская обл Москва г Ленина ул
1 Татарстан. Агрызский район. Васильево пгт. Ленина ул Татарстан Агрызский районВасильево пгт Ленина ул


В любом случае, учитывая
*Ann*
Индекса в поле adress может и не быть.

вам придется определиться с тем, как обрабатывать подобные случаи.
28 дек 18, 08:24    [21775414]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
*Ann*
Member

Откуда:
Сообщений: 57
PizzaPizza,

Уровней всего 9. Если родителя какого-то уровня нет, то ячейку надо пропустить. Уровень указан в поле level. Если нет индекса, то ячейка тоже должна остаться пустой.
28 дек 18, 10:13    [21775472]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 818
+ как-то так
IF OBJECT_ID( 'tempdb..#temp' ) IS NOT NULL DROP TABLE #temp
;
CREATE TABLE #temp ( [id] INT, [address] VARCHAR(500), [formalname] VARCHAR(100), [level] INT, [parent_id] INT )
;
INSERT INTO #temp
VALUES
( 1, '630123, Новосибирская обл, Новосибирск г, Петухова ул', 'Петухова ул', 33, 2 ),
( 2, '630123, Новосибирская обл, Новосибирск г', 'Новосибирск г', 22, 3 ),
( 3, '630123, Новосибирская обл', 'Новосибирская обл', 11, NULL )
;
WITH
cte AS (
  SELECT
    [base] = tt.[id],
    [lvl] = 1,
    tt.*
  FROM
    #temp tt
    LEFT JOIN #temp tt2 ON (
          tt2.[parent_id] = tt.[id] )
  WHERE
    tt2.[id] IS NULL
  UNION ALL
  SELECT
    [base] = cte.[base],
    [lvl] = cte.[lvl] + 1,
    tt.*
  FROM
    cte
    INNER JOIN #temp tt ON (
          tt.[id] = cte.[parent_id] )
)
SELECT
  [base],
  [address] = MAX( CASE WHEN [lvl] = 1 THEN [address] END ),
  [index]   = MAX( CASE WHEN [lvl] = 1 AND [address] LIKE '[0-9][0-9][0-9][0-9][0-9][0-9],%' THEN LEFT( [address], 6 ) END ),
  [formalname11] = MAX( CASE WHEN [level] = 11 THEN [formalname] END ),
  [formalname22] = MAX( CASE WHEN [level] = 22 THEN [formalname] END ),
  [formalname33] = MAX( CASE WHEN [level] = 33 THEN [formalname] END ),
  [formalname44] = MAX( CASE WHEN [level] = 44 THEN [formalname] END ),
  [formalname55] = MAX( CASE WHEN [level] = 55 THEN [formalname] END ),
  [formalname66] = MAX( CASE WHEN [level] = 66 THEN [formalname] END ),
  [formalname77] = MAX( CASE WHEN [level] = 77 THEN [formalname] END ),
  [formalname88] = MAX( CASE WHEN [level] = 88 THEN [formalname] END ),
  [formalname99] = MAX( CASE WHEN [level] = 99 THEN [formalname] END )
FROM
  cte
GROUP BY
  [base]
;
28 дек 18, 10:44    [21775517]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
PizzaPizza
Member

Откуда:
Сообщений: 183
*Ann*
PizzaPizza,

Уровней всего 9. Если родителя какого-то уровня нет, то ячейку надо пропустить. Уровень указан в поле level. Если нет индекса, то ячейка тоже должна остаться пустой.


Если у вас всегда четко 9 уровней, то сделайте на 9ти джойнах например.
Выберете все записи первого уровня и к ним присоединяйте self join Id=parent_id и следующий уровень, а к нему уже по Id=parent_id-предыдущего уровня и так 9 раз. А потом отдельно поиск индекса по каким то правилам в подстроке и формируете запись.
28 дек 18, 21:01    [21776056]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 818
PizzaPizza
*Ann*
PizzaPizza,

Уровней всего 9. Если родителя какого-то уровня нет, то ячейку надо пропустить. Уровень указан в поле level. Если нет индекса, то ячейка тоже должна остаться пустой.


Если у вас всегда четко 9 уровней, то сделайте на 9ти джойнах например.
Выберете все записи первого уровня и к ним присоединяйте self join Id=parent_id и следующий уровень, а к нему уже по Id=parent_id-предыдущего уровня и так 9 раз. А потом отдельно поиск индекса по каким то правилам в подстроке и формируете запись.

И в каждом последующем ты будешь писать [id_parent] = COALESCE( tn.[id], tn-1.[id],... ) ?
Сумасошел? Пойди выспись!
29 дек 18, 10:00    [21776227]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
*Ann*
Member

Откуда:
Сообщений: 57
Руслан Дамирович,

При запуске запроса выдаётся сообщение о том, что cte не существует. Мне кажется, ругается на второй select. У меня используется postgresql, но вряд ли это влияет.
9 янв 19, 06:49    [21780215]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 27525
*Ann*
При запуске запроса выдаётся сообщение о том, что cte не существует. Мне кажется, ругается на второй select. У меня используется postgresql, но вряд ли это влияет.
Думаю, у вас в коде ошибка.
9 янв 19, 08:09    [21780231]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
*Ann*
Member

Откуда:
Сообщений: 57
Подскажите, пожалуйста, postgresql не поддерживает квадратные скобки? С ними скрипт не работает. Если их убирать, то тогда как обозначить lvl =1?
17 янв 19, 05:06    [21787430]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 252
1 as lvl
17 янв 19, 06:04    [21787448]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
*Ann*
Member

Откуда:
Сообщений: 57
Убрала скобки, заменила названия полей и таблиц, в коде ничего не меняла, все равно выдается сообщение о том, что cte не существует (см. файл).

К сообщению приложен файл (Рекурсия пробная1.txt - 1Kb) cкачать
17 янв 19, 09:52    [21787508]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2034
*Ann*
Подскажите, пожалуйста, postgresql

тут века не по postgres , а MS SQL
17 янв 19, 10:22    [21787549]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
Asic
Member

Откуда:
Сообщений: 25
*Ann*,
ошибка в cte, вы ее еще не создали, а после union all ее же и используете
17 янв 19, 10:47    [21787580]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
Asic
Member

Откуда:
Сообщений: 25
select cte.base, cte.lvl from cte 

откуда им взяться?
17 янв 19, 10:50    [21787584]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсия  [new]
*Ann*
Member

Откуда:
Сообщений: 57
Как тогда правильно написать?
17 янв 19, 11:35    [21787633]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить