Получение «полных» названий местоположений из таблицы «родитель-потомок»
У меня есть таблица, содержащая названия мест и их родительские названия. Родители и дети также имеют числовые идентификаторы. Например:
id name parent_id parent_name
---- ------ ---------- -----------
1 Chicago 2 Cook
2 Cook 3 Illinois
3 Illinois 4 United States
4 United States NULL NULL
Я хочу заполнить другое поле «полным» названием места. То есть сказать что-то вроде этого:
id name parent_id parent_name FQName
---- ------ ---------- -------------- --------------------------------------
1 Chicago 2 Cook Chicago, Cook, Illinois, United States
2 Cook 3 Illinois Cook, Illinois, United States
3 Illinois 4 United States Illinois, United States
4 United States NULL NULL United States
Он рекурсивный, поэтому я подумал об использовании CTE. Но поскольку существует n уровней, и n может различаться в зависимости от местоположения (например, в такой маленькой стране, как Израиль, самый длинный уровень, который я видел бы, — это Кармиэль, Север, Израиль), я не знаю, как с этим работать. Все примеры CTE, которые я видел, идут сверху вниз, потому что вершина — это известное количество (родительский элемент = NULL).
Я уверен, что есть что-то очевидное, что я упускаю, но я не могу этого понять.
У меня осталась версия SQL Server, которая не поддерживает STRING_AGG, поэтому я тоже не могу ее использовать.
STRING_AGG был добавлен для SQL Server 2017 (14.x) и более поздних версий, поэтому я предполагаю, что вы используете SQL Server 2016. Выполните select @@version; чтобы узнать и сообщить нам.
Да, сейчас 2016 год. Это то, что использует мой хост, и они не будут его менять.
@ThomA, мне понадобится месяц, чтобы пройти через все более абсурдные вещи, которые я пробовал. Я не знаю, что такого в CTE, что заставляет мой мозг сломаться. Это одна из причин, по которой я это делаю. Думаю, если я буду работать с достаточным количеством разных вариантов, я их выясню.
Ответы 2
Рекурсивное CTE было бы правильным общим решением. Однако, если вы точно знаете, что существует максимальное количество уровней, вы можете использовать серию LEFT JOIN и функцию CONCAT_WS(), чтобы создать свое полное имя.
SELECT
P1.*,
CONCAT_WS(', ', P1.name, P2.name, P3.name, P4.name, P5.name, P6.name) AS FQName
FROM Place P1
LEFT JOIN Place P2 ON P2.id = P1.parent_id
LEFT JOIN Place P3 ON P3.id = P2.parent_id
LEFT JOIN Place P4 ON P4.id = P3.parent_id
LEFT JOIN Place P5 ON P5.id = P4.parent_id
LEFT JOIN Place P6 ON P6.id = P5.parent_id
ORDER BY P1.id
Для версий SQL Server до 2017 года, которые не поддерживают CONCAT_WS(), вы можете использовать функцию CONCAT() следующим образом:
CONCAT(P1.name, ', ' + P2.name, ', ' + P3.name, ', ' + P4.name,
', ' + P5.name, ', ' + P6.name) AS FQName
Нулевые значения будут спокойно игнорироваться.
См. эту db<>fiddle для демонстрации.
Примечание: обычно вы не храните parent_name в дочерней строке, поскольку он избыточен по отношению к родительской строке и может привести к несогласованности данных.
Но если они не могут использовать STRING_AGG(), они не могут использовать CONCAT_WS(), поскольку оба они были представлены в SQL Server 2017.
@testing-for-ya – Спасибо за отзыв. Я обновил ответ альтернативным расчетом CONCAT().
Решение SQL Server 2016/2014 с использованием рекурсивного CTE
WITH CTE as (
SELECT pn.id,
pn.name,
pn.parent_id,
cast(NULL as VARCHAR(512)) as ParentName,
pn.name as FQName
FROM placenames pn
WHERE NOT EXISTS
(SELECT 1 FROM placenames as pn2 WHERE pn2.id = pn.parent_id)
UNION ALL
SELECT b.id,
b.name,
b.parent_id,
a.Name as ParentName,
cast(b.name + ', ' + a.FQName as VARCHAR(512)) as FQName
FROM CTE a
INNER JOIN placenames b ON a.id=b.parent_id
)
SELECT * FROM CTE
ORDER BY CTE.id
Кажется, здесь вам нужен рекурсивный CTE, а не
STRING_AGG. Почему rCTE точно не работает? Какова была ваша попытка?