将一个递归查询的输出重定向到另一个递归查询 [英] Redirect output of one recursive query into another recursive query

问题描述

我想找到 DAG 的拓扑排序.

I wanted to find the topological sort of a DAG.

CREATE TABLE topo(
v1 int,
v2 int
);

INSERT INTO topo VALUES (1,3),(2,5),(3,4),(4,5),(4,6),(5,7),(6,5),(7,null)

WITH RECURSIVE path(S,d) AS(
 SELECT t1.v1, 0 FROM topo t1 LEFT OUTER JOIN topo AS t2 ON t1.v1=t2.v2 
 WHERE t2.v2 IS NULL
 UNION ALL
 SELECT DISTINCT t1.v2, path.d + 1 FROM path INNER JOIN topo AS t1 ON 
 t1.v1=path.S
)
SELECT S FROM path GROUP BY S ORDER BY MAX(d);

这段代码给出了一个图的拓扑顺序的输出.现在我想将此输出用于另一个递归查询，以查找从一个顶点到另一个顶点的路径.

This code gives the output of the topological order of a graph. Now I want to use this output into another recursive query to find the path from one vertex to another.

我可以将这段代码生成的输出用于另一个递归查询吗?

Can I use the output generated by this code into another recursive query?

推荐答案

添加到您现有的递归 sql 以获取路径:

Adding to your existing recursive sql to get path:

WITH RECURSIVE path AS(
 select 
   t1.v1 as start,
   t1.v2 as end,  
   CAST(t1.v1 as VARCHAR(30)) as path 
   0 as depth
 from topo t1 
   left outer join topo as t2 on t1.v1=t2.v2 
 where t2.v2 IS null

 UNION ALL
 select 
   path.start ,
   t1.v2 as end,
   path.path || '>' || t1.v1,       
   path.d + 1 
 from path 
   inner join topo as t1 on t1.v1=path.end
)
SELECT * FROM path

只需添加几个字段即可跟踪在您遍历层次结构时发生的情况.Start 将从第一个查询开始是静态的.每条记录都是 1，因为这是您的起点.End 是您当前正在工作的任何节点.path 将在发现每个新节点时连接结束节点.depth 会告诉你你走了多远.

Just adding in a couple of more fields to track what's happening as you traverse your hierarchy. Start will be static from the first query. It will be 1 for every record since that is your starting point. End is whatever node you are currently working. path will concatenate the end node as each new one is found. depth will tell you how far you traversed.

这篇关于将一个递归查询的输出重定向到另一个递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！