sql - 如何使用分层子查询构建层次结构路径

Question

编辑:我通过引入 location 实体来提供附加信息，以阐明我尝试使用子查询的原因

在 oracle 11g 数据库中，我有 元素 的分层结构表，它最终将包含几百万行。每行都有指向其父行的索引外键，并且不允许循环。 元素 也有名称和类型。除此之外，还有另一个实体- location，它类似于element(分层的，具有指向父级+名称的外键)。顶部元素(您的根)可以位于位置(它们由LocationId 连接)。所以有 2 个实体:

位置:

• ID [NUMBER(9,0), PK]
• 父代号 [NUMBER(9,0), FK]
• 名称 [VARCHAR2(200)]

元素:

• ID [NUMBER(9,0), PK]
• LocationId [NUMBER(9,0), FK]
• 父代号 [NUMBER(9,0), FK]
• TypeId [NUMBER(9,0), FK]
• 名称 [VARCHAR2(200)]

现在假设表格包含以下数据，例如:

位置:

Id   | ParentId | Name
----------------------------------
100  |   null   | TopLocation
101  |   100    | Level1Location
102  |   101    | Level2Location    

元素:

Id | LocationId | ParentId | TypeId | Name
----------------------------------------------------
1  |    102     |   null   |    10  | TopParent
2  |   null     |     1    |    11  | Level1Child
3  |   null     |     2    |    11  | Level2Child

我要做的是为元素编写查询，除了基本的4个元素列之外，它还返回父id、名称和类型id的完整路径+顶部元素 位置 ID 和名称。因此，如果我获取 ID 为 3 的 element(此条件也可能因此处未指定的多个列而变得复杂)，查询将必须返回此内容:

Id | ParentId | TypeId | Name        | IdsPath | TypeIdsPath | NamesPath                           | LocIdsPath   | LocNamesPath
---------------------------------------------------------------------------------------------------------------------------------------------------------------
3  |    2     |    11  | Level2Child | /3/2/1  |   /11/11/10 |  /Level2Child/Level1Child/TopParent | /102/101/100 | /Level2Location/Level1Location/TopLocation

首先我写了oracle hierarchical query它返回location 和element

所需的路径

位置

select
    SYS_CONNECT_BY_PATH(Id, '/') IdsPath,
    SYS_CONNECT_BY_PATH(Name, '/') NamesPath
from 
    loc
where
     connect_by_isleaf = 1
CONNECT BY PRIOR ParentId = e.Id
start with Id = 102

元素

select
    SYS_CONNECT_BY_PATH(Id, '/') IdsPath,
    SYS_CONNECT_BY_PATH(TypeId, '/') TypeIdsPath,
    SYS_CONNECT_BY_PATH(Name, '/') NamesPath
from 
    ele
where
     connect_by_isleaf = 1
CONNECT BY PRIOR ParentId = e.Id
start with Id = 3

当我想将这些查询用作基本选择中连接的子查询时，问题就开始了——不能用连接条件替换 start with 条件，因为分层查询比全表扫描要好:

select
    e.*,
    elePath.IdsPath,
    elePath.TypeIdsPath,
    elePath.NamesPath,
    locPath.IdsPath as LocIdsPath,
    locPath.NamesPath as LocNamesPath
from
    ele e
    left join (
        --full table scan!
        select
            CONNECT_BY_ROOT(Id) Id,
            Id as TopEleId,
            SYS_CONNECT_BY_PATH(Id, '/') IdsPath,
            SYS_CONNECT_BY_PATH(TypeId, '/') TypeIdsPath,
            SYS_CONNECT_BY_PATH(Name, '/') NamesPath
        from ele
        where
             connect_by_isleaf = 1
        CONNECT BY PRIOR ParentId = e.Id
    ) elePath on elePath.Id = e.Id
    left join (
        --full table scan!
        select
            CONNECT_BY_ROOT(Id) Id,
            SYS_CONNECT_BY_PATH(Id, '/') IdsPath,
            SYS_CONNECT_BY_PATH(Name, '/') NamesPath
        from loc
        where
             connect_by_isleaf = 1
        CONNECT BY PRIOR ParentId = e.Id
    ) locPath on locPath.Id = elePath.TopEleId
where
    e.Id = 3

我也做不到scalar subquery因为查询必须返回多条路径，而不仅仅是一条。有什么建议么？我什至是朝着正确的方向前进，还是应该向元素表添加一些字段并缓存我需要的所有路径？ (不会经常更新)

谢谢!

Answer 1

您反向遍历层次结构，只需使用 connect_by_root() 运算符即可获取根行的列值。

clear screen;
column IdPath format a11;
column TypeIdPathformat a11
column NamePath format a35;

with t1(id1, parent_id, type_id, Name1) as(
  select 1, null, 10, 'TopParent' from dual union all
  select 2, 1   , 11, 'Level1Child' from dual union all
  select 3, 2   , 11, 'Level2Child' from dual
)
select connect_by_root(id1)                as id1
     , connect_by_root(parent_id)          as ParentId
     , connect_by_root(type_id)            as Typeid
     , connect_by_root(name1)              as name1
     , sys_connect_by_path(id1, '/')       as IdPath
     , sys_connect_by_path(type_id, '/')   as TypeIdPath
     , sys_connect_by_path(name1, '/')     as NamePath 
 from t1
where connect_by_isleaf = 1
start with id1 = 3
connect by id1 = prior parent_id

结果:

 id1 ParentId TypeId  Name1        IdPath  TypeIdPath NamePath                         
 ---------------------------------------------------------------------------       
 3        2      11   Level2Child /3/2/1  /11/11/10  /Level2Child/Level1Child/TopParent

---

编辑#1

获得所需输出的一种方法是使用标量子查询:

with Locations(Id1, ParentId, Name1) as(
  select 100,  null, 'TopLocation' from dual union all
  select 101,  100 , 'Level1Location' from dual union all
  select 102,  101 , 'Level2Location' from dual
),
elements(id1, LocationId, parent_id, type_id, Name1) as(
  select 1, 102,  null, 10, 'TopParent' from dual union all
  select 2, null, 1   , 11, 'Level1Child' from dual union all
  select 3, null, 2   , 11, 'Level2Child' from dual
)
select e.*
     , (select sys_connect_by_path(l.id1, '/')
          from locations l
          where connect_by_isleaf = 1
          start with l.id1 = e.locationid
          connect by l.id1 = prior parentid)        as LocIdPath
     , (select sys_connect_by_path(l.name1, '/')
          from locations l
          where connect_by_isleaf = 1
          start with l.id1 = e.locationid
          connect by l.id1 = prior parentid)        as LocNamePath
  from ( select connect_by_root(id1)                as id1
              , connect_by_root(parent_id)          as ParentId
              , connect_by_root(type_id)            as Typeid
              , connect_by_root(name1)              as name1
              , sys_connect_by_path(id1, '/')       as IdPath
              , sys_connect_by_path(type_id, '/')   as TypeIdPath
              , sys_connect_by_path(name1, '/')     as NamePath  
              , locationid
          from elements
         where connect_by_isleaf = 1
         start with id1 = 3
       connect by id1 = prior parent_id ) e 

结果:

ID1   PARENTID     TYPEID NAME1       IDPATH      TYPEIDPATH  NAMEPATH                            LOCATIONID LOCIDPATH     LOCNAMEPATH                               
---------- ---------- ----------- ----------- ----------- ----------------------------------- ---------- ------------- -------------------------------------------
  3          2         11 Level2Child /3/2/1      /11/11/10   /Level2Child/Level1Child/TopParent         102 /102/101/100  /Level2Location/Level1Location/TopLocation