如何仅使用Oracle SQL将图表转换为邻接列表

3 回答

• 1

  with Q as (
     select level as l, is_child,
            parent_name, child_name, parent_id, child_id
       from GRAPH_TBL T,
            (select 0 as is_child from dual union select 1 from dual)
  
      start with parent_name='Xeon 5600' and parent_id=child_id
  
    connect by nocycle
            (  (child_id=prior parent_id and is_child=0)
              or
               (parent_id=prior child_id and is_child=1)
            )
        and relative_level=1
        and is_child=prior is_child
  )
  select child_name, child_id as ID, parent_id, l, is_child
    from Q
   where parent_id<>child_id
  union all
  select parent_name, parent_id, parent_id, l+1, is_child
    from Q
   where l=(select max(l) from Q where is_child=0) and is_child=0
  order by is_child, l desc
  

  结果：

  CHILD_NAME                  ID                  PARENT_ID           L   IS_CHILD
  Eclipse Products and Servic a044100000171aQAAQ  a044100000171aQAAQ  6   0
  Components                  a044100000171bXAAQ  a044100000171aQAAQ  5   0
  Processors                  a044100000171bYAAQ  a044100000171bXAAQ  4   0
  Intel                       a044100000171bZAAQ  a044100000171bYAAQ  3   0
  Xeon 5600                   a044100000171bdAAA  a044100000171bZAAQ  2   0
  Intel Xeon E5645 2.4Ghz,... a044100000171grAAA  a044100000171bdAAA  2   1
  

  回复于 2024-05-20T00:52:08+08:00
• 1

  正如@Mike在评论中提到的，你不清楚你想要什么输出 . 但这里是简单的分层查询，在这种情况下可能很有用：

  select lpad(' ', 4 * (level - 1))||child_name name
    from graph_tbl gt
    start with relative_level = 0 
    connect by relative_level <> 0 and parent_id = prior child_id
  

  另请查看 level pseudocolumn， sys_connect_by_path 和 connect_by_root ：

  select gt.relative_level, parent_name, child_name,
         level, sys_connect_by_path(child_name, '  ->  ') path, 
         connect_by_root(child_name) root 
    from graph_tbl gt
    start with relative_level = 0 
    connect by relative_level <> 0 and parent_id = prior child_id
  

  第一个查询的部分输出：

  NAME
  --------------------------------------------------------------------------------
  Eclipse Products and Services
      Components
          Processors
              Intel
                  Xeon 5600
                      Intel Xeon E5645 2.4Ghz, 12M Cache,Turbo, HT, 1333MHz Max Me
              Xeon 5600
                  Intel Xeon E5645 2.4Ghz, 12M Cache,Turbo, HT, 1333MHz Max Mem
          Intel
              Xeon 5600
                  Intel Xeon E5645 2.4Ghz, 12M Cache,Turbo, HT, 1333MHz Max Mem
          Xeon 5600
              Intel Xeon E5645 2.4Ghz, 12M Cache,Turbo, HT, 1333MHz Max Mem
  ...
  

  回复于 2024-05-20T00:52:08+08:00
• 0

  select
    TOP_LEVEL.CHILD_NAME TOP,
    TOP_LEVEL.Relative_Level,
    level_one.child_name lvl_one,
    level_one.Relative_Level,
    level_two.child_name lvl_two,
    level_two.Relative_Level,
    level_three.child_name lvl_three,
    level_three.Relative_Level,
    level_four.child_name lvl_four,
    level_five.child_name lvl_five
  from 
    graph_tbl TOP_LEVEL,
    GRAPH_TBL level_one,
    GRAPH_TBL level_two,
    GRAPH_TBL level_three,
    GRAPH_TBL level_four,
    GRAPH_TBL level_five
  where
    TOP_LEVEL.Parent_Id = TOP_LEVEL.child_id and
    level_one.parent_id = top_level.child_id and
    level_one.relative_level = 1 and
    level_two.parent_id = level_one.child_id and
    level_two.relative_level = 1 and
    level_three.parent_id = level_two.child_id and
    level_three.relative_level = 1 and
    level_four.parent_id = level_three.child_id and
    level_four.relative_level = 1 and
    level_five.parent_id = level_four.child_id and
    level_five.relative_level = 1
  

  回复于 2024-05-20T00:52:08+08:00