取消分层数据

我有一个如下所示的分层数据表:

    LEVEL   id_value    parent_id_value     description 
0   1       505         None                Top Hierarchy
1   2       1000        505                 Sub Hierarchy
2   2       300         505                 Other Sub Hierarchy
3   3       0040        300                 Rookie hierarchy
4   3       0042        300                 Bottom level

我需要的是一个查询,会给我这个:

0   id_value        3                   2                   1
1         40        Rookie hierarchy    Other Sub Hierarchy Top Hierarchy
2         42        Bottom level        Other Sub Hierarchy Top Hierarchy
3       1000        NULL                Sub Hierarchy       Top Hierarchy

它看起来应该很简单,但我错过了一些东西......


我已将您的示例数据需求转换为SQL查询。 比通知:

  • 旅程一次又一次地连接到自己的表
  • 在每个连接上使用表别名是强制性的。
  • 您可以调整此查询以符合您的一般要求。
  • 为了匹配你的数据样本,第二次连接是一个左连接
  • 这里是:

    select 
      coalesce( l3.id_value,l2.id_value) as id_value ,
      l3.description as "3",
      l2.description as "2",
      l1.description as "1"
    from t l1 inner join
         t l2 on l2."LEVEL"=2 and l1.id_value = l2.parent_id_value
              left outer join
         t l3 on l3."LEVEL"=3 and l2.id_value = l3.parent_id_value
    where l1.LEVEL = 1
    

    在sqlFiddle上检查它


    该查询提供了所有需要的信息:

    select id_value, --parent_id_value piv, description, level tlvl, 
        sys_connect_by_path(description, '/') tpath
      from hd where connect_by_isleaf = 1
      start with parent_id_value not in (select id_value from hd)
      connect by parent_id_value = prior id_value
    

    结果

    id_value  tpath
    --------  ---------------------------------------------------------------
          40  /Top hierarchy/Other sub hierarchy/Rookie hierarchy    
          42  /Top hierarchy/Other sub hierarchy/Bottom level        
        1000  /Top hierarchy/Sub hierarchy
    

    现在,如果我们假设最大层次结构深度为3,则此查询会将子层次结构置于单独的列中。

    with leaves as (
      select id_value, parent_id_value piv, description, level tlvl, 
          sys_connect_by_path(rpad(description, 20), '/') tpath
        from hd where connect_by_isleaf = 1
        start with parent_id_value not in (select id_value from hd)
        connect by parent_id_value = prior id_value )
    select id_value, 
        substr(tpath, 2*20 + 4, 20) l3, 
        substr(tpath, 1*20 + 3, 20) l2,
        substr(tpath, 0*20 + 2, 20) l1
      from leaves
    
    =====================================================================
    id_value  L3                    L2                      L1 
          40  Rookie hierarchy      Other sub hierarchy     Top hierarchy       
          42  Bottom level          Other sub hierarchy     Top hierarchy       
        1000                        Sub hierarchy           Top hierarchy       
    

    如果描述长度> 20,则将此值更改为字段列长度。

    这也可以很容易地在PL / SQL中动态完成,例如首先计算深度,通过execute immediate并将层次结构放入右列来创建具有适当列数的表。


    不知道为什么你需要LEVEL列,但简单的分层查询应该工作。 如果有一个固定数量的级别,只需将CONNECT_BY_PATH分割为几列即可:

    -- sample table
    CREATE TABLE TT1
     (ID_VALUE          NUMBER,
      PARENT_ID_VALUE   NUMBER,
      DESCRIPTION       VARCHAR2(32));
    
    -- the query itself
     SELECT ID_VALUE,
            REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(DESCRIPTION, '/'),  '[^/]+', 1, 3) L3,
            REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(DESCRIPTION, '/'),  '[^/]+', 1, 2) L2,
            REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(DESCRIPTION, '/'),  '[^/]+', 1, 1) L1
       FROM TT1
      WHERE CONNECT_BY_ISLEAF = 1
      START WITH PARENT_ID_VALUE IS NULL
    CONNECT BY PARENT_ID_VALUE = PRIOR ID_VALUE
    
    链接地址: http://www.djcxy.com/p/93917.html

    上一篇: unstack hierarchical data

    下一篇: Getting parent data if child data is null in Oracle hierarchical table