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

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

假设有两个表：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)