我已经进行了研究,知道我不是第一个提问的人,但我似乎无法理解它。我创建了一个简单的示例,如果有人能提供缺失的链接,我认为这将帮助我解决它!
我有一个包含大洲和国家层次结构的区域表。
我还有一个包含城市和地标层次结构的地点表。此表包含一个区域ID列,以连接到区域表。
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);
我可以像这样独立查询这些表:
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工作多年了,但不知何故从未遇到过这种情况!
如果places表中没有connect by prior,只有一个带有区域ID的城市列表,那么这是否更容易?
谢谢
connect by
,则应将所有行合并为一个记录集。我使用了负ID的技巧,但这并不是必要的。您可以使连接更精确,例如type = prior type and parent_id = prior id
。 - Sanders the Softwarer