给定任何节点作为输入的Oracle SQL完整层次结构 [英] Oracle SQL full hierarchy given any node as input

问题描述

我想在Oracle SQL数据库中创建一个完整的层次搜索查询. 我有一张下表，称为项目".

I would like to create a full hierarcy search query in Oracle SQL database. I have a following table, called "item".

id   name  parent_id
1    A     NULL
2    B     1
3    C     2
4    D     3
5    E     2

输入是id列中任何给定的id.它应该找到此id的所有孩子，即他们的孩子.以及父母和他们的父母. 目前，我有以下查询:

Input is any of a given id from id column. It should find all of this id's children, their children. As well parents and their parents. At the moment I have following query:

select distinct m.id, m.parent_id
    from item m
connect by prior m.id = m.parent_id
    start with m.parent_id IN (
        select m.parent_id
        from item m
        connect by m.id = prior m.parent_id
    start with m.id = 3
    union
    select m.parent_id
        from item m
    where m.parent_id = 3);

此刻，它似乎仅在起作用，因此未选择没有父项的父项(parent_id列为null).否则，它似乎正在工作.另外，如果我的给定查询可以简化，我也将不胜感激.

At the moment it seems to be working only so that parent which does not have parent (parent_id column is null) is not selected. Otherwise it seems to be working. Also if my given query could be simplified I would appreciate it as well.

编辑

我认为我通过以下查询获得了预期的结果:

I think I got the desired result with following query below:

select m.id
    from item m
    start with m.id in (
        select m.id
        from item m
        where connect_by_isleaf = 1
        start with m.id = 3
        connect by m.id = prior m.parent_id
    )
    connect by m.parent_id = prior m.id;

现在有下一个问题. 以m.id = 3 开头.问题是我想从整个查询中创建一个视图.但是随着 m.id 值在查询之间变化，我无法将其添加为参数.也有可能注释掉以m.id = 3 开头，然后它将返回所有项目之间的所有层次结构.有没有办法创建一些联接?例如:我将查询所有商品的所有那些关系，然后在某种条件下仅获得某些商品的关系.

Now there is next issue I have. start with m.id = 3. Issue is that I would like to create a view out of this whole query. But as m.id value changes from query to query I cannot add it as a parameter. There's also a possibility to comment out start with m.id = 3 and then it would return all hierarchies between all items. Is there a way to create some join? E.g.: I would query all those relations of all items and then by some condition get only certain item relations.

推荐答案

如果要将其用作视图，可以执行以下操作:

If you want to use it as view you can do something as the following:

WITH rek AS (SELECT item.id
                  , item.name
                  , connect_by_root item.id root_id
               FROM item
              START WITH parent_id IS null
            CONNECT BY NOCYCLE parent_id = PRIOR id)
SELECT startItem.id startId
     , startItem.name startName
     , childItem.id childID
     , childItem.name childName
  FROM rek startItem
  JOIN rek childItem
  USING (root_id)
-- WHERE startItem.id = 3 -- This would be done from outside the view

子查询rek将树的所有同级与根元素连接起来.然后，您只需使用两次该查询，然后通过root元素进行连接即可获取通过父子关系连接的所有元素.

The subquery rek connects all sibling of the tree with the root element. Then you only have to use this query twice and connect it via the root element to get all elements that are connected via parent-child relation.

如果要减少结果集，可以使用SYS_CONNECT_BY_PATH来这样做:

If you want to reduce the resultset you can use the SYS_CONNECT_BY_PATH to do so:

WITH rek AS (SELECT item.id
                  , item.name
                  , connect_by_root item.id root_id
                  , SYS_CONNECT_BY_PATH(item.id, '/') path
               FROM item
              START WITH parent_id IS null
            CONNECT BY NOCYCLE parent_id = PRIOR id)
SELECT startItem.id startId
     , startItem.name startName
     , childItem.id childID
     , childItem.name childName
     , childItem.path 
  FROM rek startItem
  JOIN rek childItem
    ON startItem.root_id = childItem.root_id
    AND (startItem.path LIKE childItem.path||'/%'
      OR childItem.path LIKE startItem.path||'/%'
      OR childItem.id = startItem.id)

例如，这只会给您您的孩子的父母，而不是其他叶子的条目.

This for example will give you only the childs and parents of you starting point and no entries from other leafs.

这篇关于给定任何节点作为输入的Oracle SQL完整层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！