对Oracle层次结构查询使用多个表，其中每个表代表一个级别

xzlaal3s 于 2023-02-07 发布在 Oracle

关注(0)|答案(1)|浏览(135)

我正在研究如何为我的Oracle数据库创建分层SQL查询，其中Table 1和Table 2具有多对一关系，Table 2和Table 3具有多对一关系，Table 1是Table 2的父级，Table 2是Table 3的父级。
我正在尝试创建一个查询，其中表1表示Oracle的LEVEL伪列中的LEVEL 1，表2表示LEVEL2，表3表示LEVEL 3。
简化的表结构如下所示，其中TABLE 1、TABLE 2和TABLE 3各自具有唯一的列以及它们自己的唯一ID和描述。
例如，假设表1表示一个州，表2表示一个城市，表3表示一个邮政编码--这些都有自己唯一的属性，一个州有许多城市，一个城市有许多邮政编码。

TABLE1(id (PK), t1name, description)
TABLE2(id (PK), t2name, table1ID (FK), description, var1, var2)
TABLE3(id (PK), t3name, table2ID (FK), description, var3, var4)

我试过使用这样的查询，但是LEVEL伪列总是'1'，CONNECT_BY_ISLEAF伪列也是'1'：

WITH alltabs as
    (Select 'T1' as src, table1.ID, NULL AS parent1Id, NULL as parent2Id, table1.name as name
    from table1
    union
    Select 'T2' as src, table2.ID, table2.table1Id, NULL as parent2Id, table2.name as name
    from table2
    union
    Select 3 as src, table3.ID, NULL AS parent1Id, table3.table2id, table3.name as name
    from table3)
Select LEVEL, src, parent1Id, parent2Id, name, CONNECT_BY_ISLEAF
from alltabs
connect by (id = parent1Id and (src = 'T1' or src = 'T2'))
        or (id = parent2Id AND (src = 'T2' or src = 'T3'))

我要查找的结果是LEVEL伪列在表1中为“1”，在表2中为“2”，在表3中为“3”。
任何指导将不胜感激-我也开放改变表的结构。谢谢！

oracle

来源：https://stackoverflow.com/questions/75366620/using-multiple-tables-for-an-oracle-hierarchy-query-where-each-table-represents

1条答案

按热度按时间

csga3l581#

只需使用带有自引用外键的单个表：

CREATE TABLE table1 (
  id     NUMBER
         CONSTRAINT table1__id__pk PRIMARY KEY,
  name   VARCHAR2(50),
  parent CONSTRAINT table1__parent__fk REFERENCES table1 (id)
);

然后您可以使用：

SELECT t.*,
       LEVEL,
       CONNECT_BY_ISLEAF,
       SYS_CONNECT_BY_PATH(name, ' > ') AS ancestors
FROM   table1 t
START WITH parent IS NULL
CONNECT BY PRIOR id = parent
ORDER SIBLINGS BY name;

其中，对于示例数据：

INSERT INTO table1 (id, name, parent)
SELECT 1, 'Alice', NULL FROM DUAL UNION ALL
SELECT 2, 'Beryl', 1 FROM DUAL UNION ALL
SELECT 3, 'Carol', 1 FROM DUAL UNION ALL
SELECT 4, 'Debra', 2 FROM DUAL UNION ALL
SELECT 5, 'Emily', 3 FROM DUAL UNION ALL
SELECT 6, 'Fiona', 4 FROM DUAL;

输出：
| 识别号|姓名|母体|水平|按隔离叶连接|祖先|
| - ------|- ------|- ------|- ------|- ------|- ------|
| 1个|爱丽丝|* 无效 *|1个|无|〉爱丽丝|
| 第二章|绿柱石|1个|第二章|无|〉爱丽丝〉绿柱石|
| 四个|黛布拉|第二章|三个|无|〉爱丽丝〉绿柱石〉黛布拉|
| 六个|菲奥娜|四个|四个|1个|〉爱丽丝〉贝里尔〉黛布拉〉菲奥娜|
| 三个|卡罗尔|1个|第二章|无|〉爱丽丝〉卡罗尔|
| 五个|艾米莉|三个|三个|1个|〉爱丽丝〉卡罗尔〉艾米丽|
fiddle

更新

假设表1表示一个州，表2表示一个城市，表3表示一个邮政编码--这些都有自己唯一的属性，一个州有许多城市，一个城市有许多邮政编码。
如果您有三个不同的数据集（州、城市和邮政编码），每个数据集的属性都是唯一的，那么请使用三个不同的表。

CREATE TABLE state (
  id                    NUMBER PRIMARY KEY,
  name                  VARCHAR2(50),
  government            VARCHAR2(50),
  flag                  BLOB
);

CREATE TABLE city (
  id                    NUMBER PRIMARY KEY,
  state_id              REFERENCES state (id),
  mayor                 VARCHAR2(20),
  rat_population        NUMBER(4,0)
);

CREATE TABLE zip_code (
  id                    NUMBER PRIMARY KEY,
  state_id              REFERENCES state (id),
  nearest_city_id       REFERENCES city (id),
  refuse_collection_day VARCHAR2(9)
);

如果要查找有关表的信息，则在要组合两个或更多数据集时，只需在引用约束条件上使用JOIN。您不希望对这种类型的数据使用层次查询。

赞(0）回复(0）举报 2023-02-07

我来回答

对Oracle层次结构查询使用多个表，其中每个表代表一个级别

1条答案

更新

相关问题

热门标签

最新问答