使用SQL语句查询形成环路的关系数据

最新推荐文章于 2024-05-26 14:11:49 发布

莱昂纳多达芬奇

最新推荐文章于 2024-05-26 14:11:49 发布

阅读量1.8k

点赞数 1

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/davinciyxw/article/details/8113136

版权

数据库专栏收录该内容

11 篇文章

订阅专栏

数据如下：

若使用递归查询则可能导致SQL执行异常，需要查找并删除这条bug数据。

WITH ROOT_NODE (
        parent)
     AS
        (SELECT   parent
           FROM   TABLE1
          WHERE   parent LIKE 'grandpa' || '%'), --若已知大概范围
     FIND_CALL_RST (
        SVC,
        parent,
        child,
        CALL_LEVEL,
        VAL)
     AS
        (SELECT   parent AS SVC,
                  parent,
                  child,
                  1 AS CALL_LEVEL,
                  CAST (
                     parent || '->' || child AS VARCHAR (1000))
                     VAL
           FROM   TABLE1
          WHERE   parent IN (SELECT   parent FROM ROOT_NODE)
         UNION ALL
         SELECT   VT.SVC,
                  T.parent,
                  T.child,
                  VT.CALL_LEVEL + 1,
                  VT.VAL || '->' || T.child AS VAL
           FROM   FIND_CALL_RST VT, TABLE1 T
          WHERE   T.parent = VT.child AND CALL_LEVEL < 20) 
  SELECT   CALL_LEVEL, A.VAL
    FROM   FIND_CALL_RST A
   WHERE   A.CALL_LEVEL > 19

结果：

-------------------

20	grandpa->father->son->grandchild->grandpa->father->son->grandchild->grandpa->father->son->grandchild->grandpa->father->son->grandchild->grandpa->father->son->grandchild->grandpa

-------------------