时间戳

首页 > 解决方案 > 获取特定个体的所有孩子（基于树的层次结构）

sql - 获取特定个体的所有孩子（基于树的层次结构）

问题描述

我目前正在尝试获取树的任何特定位置的每个关系和孩子。表格如下：

TABLE ORGANISATION                      TABLE LINKS
| Orga_Name | Code  | DELETED |         | Code  | Dads_Code |  STT  |
| DR        | DR001 |  FALSE  |(root)   | DR001 |   Null    |   OK  |(root no dad snif)
| DSI       | DS001 |  FALSE  |         | DS001 |   DR001   |   OK  |
| DLE       | DL001 |  FALSE  |         | DL001 |   DR001   |   OK  |
| DMP       | DM001 |  FALSE  |         | DM001 |   DS001   |   OK  |
| TRS       | TR001 |  FALSE  |         | TR001 |   DM001   |   OK  |
| TRE       | TE001 |  TRUE   |         | TE001 |   DM001   |   NOK |
| TRC       | TC001 |  FALSE  |         | TE001 |   DM001   |   NOK |

如果任何行的 DELETED = TRUE 或 STT = NOK，则根本不应该考虑它。（就像本例中的 TRE 和 TRC）

完美的结果如下：

|  Code  |   PICK-ONE|  Path                     |
|  DS001 |   DR001   |  DS001/DR001              |
|  DL001 |   DR001   |  DL001/DR001              |
|  DM001 |   DR001   |  DM001/DS001/DR001        |
|  TR001 |   DR001   |  TR001/DM001/DS001/DR001  |
|  DM001 |   DS001   |  DM001/DS001              |
|  TR001 |   DS001   |  TR001/DM001/DS001        |
|  TR001 |   DM001   |  DM001/TR001              |

所以我可以在“PICK-ONE”中选择一个代码，并将它的所有（直接和间接）子代作为一个列表。我一直在尝试使用 CONNECT_BY_ROOT，这是我正在使用的当前代码：

    SELECT  Code, 
            CONNECT_BY_ROOT Code,
            LEVEL-1, 
            SYS_CONNECT_BY_PATH(Code, '/') "Path"--,STT

    FROM    (
            SELECT o1.*,a1.STT,o1.DELETED
            FROM ORGANISATION o1
            LEFT JOIN LINKS a1
            ON o1.Code=a1.Code
            AND DELETED = 'FALSE'
            )
    WHERE LEVEL > 0 AND STT like 'OK' 
    CONNECT BY PRIOR Code = Dads_Code;

我主要从根源和他的一个（总是相同的）孩子中获得结果。在我的示例中，我得到的结果将来自 DS001 和 DR001。 有些结果是正确的。 可悲的是，分析停止在最大 2 级，有一些重复，还有一些通往自身结果的路径（以 0 作为级别）。

我一直在尝试更改级别或路径，或更改某些条件，但我无法找到问题所在。如果你能帮助我，我会很高兴！

标签： sqloracletree

解决方案

请勿使用CONNECT BY，因为此时它已基本过时。在没有任何标准的时代，这是查询层次结构的好方法。

使用大多数数据库中可用的递归公用表表达式（又名递归 CTE）作为 SQL 标准的一部分。

例如，如果要检索 的所有子项DR001，可以执行以下操作：

with n (code, dads_code, lvl, path) as (
  select code, dads_code, 1, code from links where code = 'DR001'
  union all
  select l.code, l.dads_code, n.lvl + 1, n.path || '/' || l.code
    from links l
    join n on n.code = l.dads_code
    where l.stt <> 'NOK'
      and not exists (select 1 from organisation o 
                      where o.code = l.code and deleted = 'TRUE')
)
select code, dads_code, lvl, path from n

推荐阅读