数据如下:
若使用递归查询则可能导致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 |
-------------------