sql - 如何在包含多个表的postgres中查询树结果集

Question

假设我有以下表格:

drop table if exists city; 
drop table if exists country; 
drop table if exists world; 

create table world (
  id integer PRIMARY KEY, 
  name text not null, 
  population bigint not null
)
;
create table country (
  id integer PRIMARY KEY,
  world_id integer REFERENCES world (id),  
  name text not null, 
  population bigint not null
);

create table city (
  id integer PRIMARY KEY, 
  country_id integer REFERENCES country (id), 
  name text not null, 
  population bigint not null
);

insert into world (id, name, population) values (1, 'World', 7125000000);
insert into country (id, world_id, name, population) values (2, 1, 'Austria', 8000000); 
insert into country (id, world_id, name, population) values (3, 1, 'Poland', 38530000); 
insert into city (id, country_id, name, population) values (4, 2, 'Vienna',  1741000);
insert into city (id, country_id, name, population) values (5, 2, 'Salzburg',  145000);
insert into city (id, country_id, name, population) values (6, 3, 'Warsaw',  1710000);
insert into city (id, country_id, name, population) values (7, 3, 'Stetin',  409000);

所以基本上是一个非常简化的世界 View ，使用 3 个相互连接的表。现在为了获得我需要的所有信息，我可以简单地执行像

这样的查询

select 
    w.name, 
    c.name, 
    ci.name
from 
    world w
    left outer join country c on (w.id = c.world_id)
    left outer join city ci on (c.id = ci.country_id)

这给了我必要的数据:

 name  |  name   |   name   
-------+---------+----------
 World | Austria | Vienna
 World | Austria | Salzburg
 World | Poland  | Warsaw
 World | Poland  | Stetin

对于像这样的小例子，结果集中的数据重复可能没问题。但在我的真实示例中，结果集要大得多(20 万行)并且要查询的列也多得多。由于整个结构让人想起树结构，我想知道是否有可能构建一个看起来更像这样的结果集:

 id    | parent   |  name    | population 
-------+----------+----------+------------
 1     | null     | World    | 7125000000
 2     | 1        | Austria  |    8000000
 3     | 1        | Poland   |   38530000
 4     | 2        | Vienna   |    1741000
 5     | 2        | Salzburg |     145000
 6     | 3        | Warsaw   |    1710000
 7     | 3        | Stetin   |     409000

所有 id 在不同的表中都是唯一的。只有一个表来表示和构建树结果集，我读到，可以使用 with-queries 但到目前为止，我没有找到任何关于如何为涉及的多个表实现这一点的信息。

Answer 1

您首先需要为所有三个表创建一个“统一” View ，例如像这样:

select id, null, name, population 
from world
union all 
select id, world_id, name, population 
from country
union all
select id, country_id, name, population
from city

这可以用来构建遍历树的递归公用表表达式:

with recursive regions (id, parent_id, name, population) as (
     select id, null, name, population 
     from world
     union all 
     select id, world_id, name, population 
     from country
     union all
     select id, country_id, name, population
     from city
), region_tree as (
   select id, parent_id, name, population 
   from regions
   where parent_id is null
   union all
   select c.id, c.parent_id, c.name, c.population
   from regions c
     join region_tree p on p.id = c.parent_id
)
select * 
from area_tree;

如果您经常需要这种查询，最好将所有内容存储在一个单个 表中，其中有一列指示该行属于“区域”的类型(世界、国家、城市).这也将使引入新的“区域类型”变得更加容易(县、州等)，例如:

create table region
(
  id integer primary key, 
  parent_id integer references region,
  name text, 
  population integer,
  type text not null check (type in ('world','country','city'))
);