SQL最高级别的父级返回

Question

我有一个表，它具有父/子结构的多级AssetID。我想选择最高级别的父资产为了简单起见，它是这样的：

AssetID ParentAssetID城市1 0城市2 0城市3 0郊区11城市1郊区12城市1郊区13城市1郊区21城市2郊区22城市2郊区23城市2郊区31城市3郊区32城市3郊区33城市3街道11郊区11街道12郊区12街道13郊区13街道21郊区21街道22郊区22街道23郊区23街道31郊区31街道32郊区32街道33郊区33

和第二张这样的表：

AssetID响应街道31 1郊区21 2城市2 3Street12 4

我想做的是为每个回复选择最高级别的家长，在本例中为城市。例如，响应1将是城市1，响应2将是城市2等。

或者类似的东西

城市响应城市3 1城市2 2城市2 3city1 4

Answer 1

这是使用postgresql的一种方法。根字段告诉所有资产id的父项。这是使用递归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.5&fiddle=1460bda8 e6676fe823e61fe7aae57dc