sql - 是否可以创建层次结构但在 Postgres 中有多个行？

Question

我可以用一行结果创建层次结构(使用来 self 的 previous question 的查询)，但是当它有多行时我不知道从哪里开始。

这是我的表格:

     id|  parent_id|                      name|
-------|-----------|--------------------------|
      1|     [NULL]|             United States|
      2|     [NULL]|                     Japan|
      3|          1|                  New York|
      4|          3|                  Brooklyn|
      5|          6|               Los Angeles|
      6|          1|                California|
      7|          6|                     Dixon|
      8|          2|                     Kyoto|
      9|          2|                     Tokyo|

name 具有 null parent_id 是祖 parent 。那么，如何进行生成这样的结果的查询呢？

                   Country|    States/Province|           City|
|-------------------------|-------------------|---------------|
|            United States|           New York|       Brooklyn|
|            United States|         California|    Los Angeles|
|            United States|         California|          Dixon|
|                    Japan|              Kyoto|         [NULL]|
|                    Japan|              Tokyo|         [NULL]|

Answer 1

看起来像表上的简单自连接，因为您感兴趣的级别数有限:

select co.name as country,
       p.name as "State/Province", 
       ct.name as city
from hierarchy co
  left join hierarchy p on p.parent_id = co.id
  left join hierarchy ct on ct.parent_id = p.id
where co.parent_id is null
order by co.name;

在线示例:https://rextester.com/UON36358