dolphindb里面如何实现类似sql中递归查询方式进行无级叶子节点查询

with recursive at as(

SELECT id, name,json_array(id) as path_id,json_array(name) as path_name,false as cycle

FROM 表1

where pid is null and dir_group = 4

union ALL

SELECT t1.id, t1.name,JSON_MERGE_PRESERVE(at.path_id,json_array(t1.id)) as path_id ,

JSON_MERGE_PRESERVE(at.path_name,json_array(t1.name)) as path_name ,

json_contains(at.path_id,json_array(t1.id)) as cycle

FROM 表1 t1 join at on t1.pid=at.id

where t1.pid is not null and not at.cycle)

select * from at

0 条评论
分类：技术问答

默认排序时间排序

2 个回答

Wendyaa 2023-05-08 09:19

可以参考下面的代码，但是需要自己确定迭代次数

t=table(1..7 as nodeID,`a1`a2`a3`a4`a5`a6`a7 as nodeName,0 1 1 2 2 3 3 as parentID)
def getchildnode(t1,t){
        return select * from t1 union select t.nodeID,t.nodeName,t1.pathid+"->"+string(t.nodeID) as pathid,t1.pathname+"->"+t.nodeName as pathname from ej(t1,t,`nodeID,`parentID)
}
t1=select nodeID,nodeName,string(nodeID) as pathid,nodeName as pathname from t where parentID=0
g = def (x,a)-> x.size()<a
reduce(getchildnode{,t},  g{,t.size()}, t1)

xlli 2024-01-24 15:23

之前的方法可以减少代码量

t = table(1..7 as nodeID,`a1`a2`a3`a4`a5`a6`a7 as nodeName, 0 1 1 2 2 3 3 as parentID)
def getchildnode(t1,t):select * from t1 union select t.nodeID, t.nodeName, t1.pathid + "->" + string(t.nodeID) as pathid,t1.pathname + "->" + t.nodeName as pathname from ej(t1,t,`nodeID,`parentID)
t1 = select nodeID, nodeName, string(nodeID) as pathid, nodeName as pathname from t where parentID=0
reduce(getchildnode{,t}, t.size(), t1)

dolphindb里面如何实现类似sql中递归查询方式进行无级叶子节点查询

2 个回答

相似问题