连接两个层次查询以形成更大的层次 [英] Joining two Hierarchical queries to form larger Hierarchy

问题描述

我已经对此进行了研究，并且知道我不是第一个问这个问题的人，但是我似乎无法解决这个问题.我创建了一个简单的示例，如果有人可以提供缺少的链接，我认为它将帮助我破解它！

I have researched this and know I'm not the first to ask but I can't seem to get my head around it. I have created a simple example that I think will help me crack it if someone can provide the missing link!

我有一张表格，其中包含按层次结构划分的各洲和各个国家/地区.

I have a table of areas that contains continents and countries in a hierarchy.

我还有一张地方表，其中包含城市和地标的层次结构.该表包含一个区域ID列，用于连接到区域表.

I also have a table of places that contains cities and landmarks in a hierarchy. This table contains an area id column to join to the areas table.

    create table areas
(
  id            NUMBER not null,
  name          VARCHAR2(200) not null,
  parent_id     NUMBER
);

-- Top Level
Insert into areas (id, name)
 Values (1, 'Europe');
Insert into areas (id, name)
 Values (2, 'Americas');
Insert into areas (id, name)
 Values (3, 'Asia ex Japan');
Insert into areas (id, name)
 Values (4, 'Japan');

 -- Jurisdictions
Insert into areas (id, name, parent_id)
 Values (5, 'UK', 1);
Insert into areas (id, name, parent_id)
 Values (7, 'France', 1);
Insert into areas (id, name, parent_id)
 Values (6, 'Germany', 1);
Insert into areas (id, name, parent_id)
 Values (8, 'Italy', 1);
Insert into areas (id, name, parent_id)
 Values (9, 'US', 2);
Insert into areas (id, name, parent_id)
 Values (10, 'Australia', 3);
Insert into areas (id, name, parent_id)
 Values (11, 'New Zealand', 3);

create table places
(
  id            NUMBER not null,
  name          VARCHAR2(200) not null,
  area_id       NUMBER,
  parent_id     NUMBER
);

Insert into places (id, name, area_id, parent_id)
 Values (1, 'London', 5, NULL);
Insert into places (id, name, area_id, parent_id)
 Values (2, 'Bath', 5, NULL);
Insert into places (id, name, area_id, parent_id)
 Values (3, 'Liverpool', 5, NULL);
Insert into places (id, name, area_id, parent_id)
 Values (4, 'Paris', 7, NULL);
Insert into places (id, name, area_id, parent_id)
 Values (5, 'New York', 9, NULL);
Insert into places (id, name, area_id, parent_id)
 Values (6, 'Chicago', 9, NULL);
Insert into places (id, name, area_id, parent_id)
 Values (7, 'Kings Cross', 5, 1);
Insert into places (id, name, area_id, parent_id)
 Values (8, 'Tower of London', 5, 1);

我可以像这样独立查询这些表:

I can query these tables independently like this:

 SELECT a.*, level FROM areas a
start with parent_id is null
connect by prior id = parent_id

SELECT p.*, level FROM places p
start with parent_id is null
connect by prior id = parent_id

有人能告诉我最后一步，将它们分为四个级别的一个查询吗?我已经在Oracle上工作了多年，但是以某种方式却从未实现过！

Is someone able to show me the last step to join these into one query with four levels? I've been working with Oracle for years but somehow this never came up!

如果places表中没有以前的连接，那么仅列出一个具有区域ID的城市，这会更容易吗?

If there was no connect by prior in the places table, just a list of cities with an area id, would this be easier?

谢谢

推荐答案

是您需要的吗?

with src as (
  select 'A' type, a.id, a.name, a.parent_id, null area_id from areas a
  union all
  select 'P', -p.id id, p.name, -p.parent_id parent_id, area_id from places p)
select 
  src.*, level
from 
  src
start with 
  type = 'A' and parent_id is null
connect by 
  parent_id = prior id or 
  parent_id is null and area_id = prior id

这篇关于连接两个层次查询以形成更大的层次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！