已知开始节点和结尾节点的条件，求图中的所有路径及权

最新推荐文章于 2025-11-05 10:22:24 发布

原创最新推荐文章于 2025-11-05 10:22:24 发布 · 695 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#n2 #join #c

MSSQL 专栏收录该内容

39 篇文章

订阅专栏

本文介绍了一个使用SQL递归查询的方法来找出工作流图中从指定起始节点到结束节点的所有可能路径及其权重总和。通过定义递归公用表表达式（CTE），实现了对工作流节点表（tblWorkFlowNode）和工作流节点间连接表（tblWorkFlowNodeNext）的有效遍历。

已知开始节点和结尾节点的条件，求图中的所有路径及权

假设有两个表：tblWorkFlowNode，tblWorkFlowNodeNext

DECLARE @workFlowId int
SET @workFlowId=106
with dept as
(
SELECT nx.NextNode,nx.workflowNodeID AS ParentNodeID
,convert(varchar(max),workflowNodeID)+','+convert(varchar(max),NextNode) as NodeIDPath
,(n.StandardWorkDay+n2.StandardWorkDay) AS dayCount
from tblWorkFlowNodeNext nx
JOIN tblWorkFlowNode n ON n.WorkFlowId=nx.WorkFlowId AND n.Node =nx.workFlowNodeId
JOIN tblWorkFlowNode n2 ON n2.WorkFlowId=nx.WorkFlowId AND n2.Node=nx.NextNode
WHERE nx.WorkFlowID=@workFlowId
AND nx.workflowNodeID NOT IN (SELECT NextNode FROM tblWorkFlowNodeNext WHERE WorkFlowID=@workFlowId)

UNION all

select nx.NextNode AS ID,nx.workflowNodeID AS ParentNodeID
,convert(varchar(max),c.NodeIDPath)+','+convert(varchar(max),nx.NextNode)
,c.dayCount+(SELECT StandardWorkDay FROM tblWorkFlowNode n where n.WorkFlowId=nx.WorkFlowId AND n.Node =nx.workFlowNodeId)
from tblWorkFlowNodeNext as nx
join dept c on nx.workflowNodeID=c.NextNode
WHERE nx.WorkFlowID=@workFlowId
)
select NodeIDPath,dayCount from dept
WHERE nextNode NOT IN (SELECT workflowNodeID FROM tblWorkFlowNodeNext WHERE WorkFlowID=@workFlowId)