#sql
#sql
Вопрос:
У меня есть таблица с многоуровневым идентификатором активов с родительской / дочерней структурой. Я хочу выбрать родительский ресурс самого высокого уровня для простоты, это так:
AssetID ParentAssetID City1 0 City2 0 City3 0 Suburb11 City1 Suburb12 City1 Suburb13 City1 Suburb21 City2 Suburb22 City2 Suburb23 City2 Suburb31 City3 Suburb32 City3 Suburb33 City3 Улица11 Пригород11 Street12 Suburb12 Street13 Suburb13 Street21 Suburb21 Улица22 Пригород22 Street23 Suburb23 Улица31 Пригород31 Улица32 Пригород32 Улица33 Пригород33
и вторая таблица, подобная этой:
Ответ AssetID Улица 31 1 suburb21 2 City2 3 Улица 12 4
Что я хотел бы сделать, так это выбрать самый высокий уровень родительского элемента, в данном случае city, для каждого из ответов. например, ответом 1 будет city1, ответом 2 будет город 2 и т.д.
Или что-то вроде этого
Ответ города City3 1 City2 2 City2 3 city1 4
Комментарии:
1. какую СУБД вы используете?
2. Street31 связан с родительским City3, не так ли
3. Джордж — да, вы правы. изменит вопрос, чтобы избежать путаных ответов.
Ответ №1:
Это можно сделать с помощью postgresql. В корневом поле указывается родительский код для всех идентификаторов активов. Это создается с использованием рекурсивного cte
with recursive cte
as (select assetid,parentassetid,assetid as root
from t
where parentassetid='0'
union all
select b.assetid,b.parentassetid,a.root
from cte a
join t b
on a.assetid=b.parentassetid
)
select *
from cte
---------- --------------- -------
| assetid | parentassetid | root |
---------- --------------- -------
| City1 | 0 | City1 |
| City2 | 0 | City2 |
| City3 | 0 | City3 |
| Suburb11 | City1 | City1 |
| Suburb12 | City1 | City1 |
| Suburb13 | City1 | City1 |
| Suburb21 | City2 | City2 |
| Suburb22 | City2 | City2 |
| Suburb23 | City2 | City2 |
| Suburb31 | City3 | City3 |
| Suburb32 | City3 | City3 |
| Suburb33 | City3 | City3 |
| Street11 | Suburb11 | City1 |
| Street12 | Suburb12 | City1 |
| Street13 | Suburb13 | City1 |
| Street21 | Suburb21 | City2 |
| Street22 | Suburb22 | City2 |
| Street23 | Suburb23 | City2 |
| Street31 | Suburb31 | City3 |
| Street32 | Suburb32 | City3 |
| Street33 | Suburb33 | City3 |
---------- --------------- -------
https://dbfiddle.uk/?rdbms=postgres_9.5amp;fiddle=1460bda8e6676feb823e61fe7aae57dc
Комментарии:
1. Это потрясающе. Не могли бы вы немного объяснить код? я новичок в расширенных функциях SQL, помимо Select, form, where … и т. д
2. рекурсивный cte состоит из двух частей -> части привязки (это записи верхнего уровня «, где parentassetid =’0′), и части, где записи соединяются обратно с основной таблицей, используя условие. («присоединитесь к записи, где родительский идентификатор моей текущей строки совпадает с идентификатором привязки) и делайте это до тех пор, пока не найдете никаких записей. Столбец «root» используется для вывода элемента привязки, который остается неизменным на протяжении всего обхода
3. Следующая ссылка предоставляет пример towardsdatascience.com /…