SQL Server 2008递归查询

Question

FromID               ToID     
--------------    ----------    
   1              2               
   2              3                 
   3              4                 
   5              6                
   6              7                
   9              10

我想在SQL Server 2008中使用递归查询来创建一个输出

FromID                Path
1                     1,2,3,4
5                     5,6,7                                          
9                     9,10

我一直在尝试使用参考在线示例构建SQL语句，如下

;WITH items AS (
  SELECT FromID
  , CAST(FromID AS VARCHAR(255)) AS Path
  FROM tablex
  UNION ALL
  SELECT i.FromID
  , CAST(Path + '.' + CAST(i.FromID AS VARCHAR(255)) AS VARCHAR(255)) AS Path
  FROM tablex i
  INNER JOIN items itms ON itms.FromID = i.ToID
)
SELECT * 
FROM items 
ORDER BY Path

然而，上面没有工作。什么好主意吗?

Answer 1

我不完全清楚为什么您期望的输出是这样的。从6-10的路径不是到10的完整路径:该路径从ID 5开始。我已经编写了一个输出完整路径的示例，以说明如何执行此操作。如果出于某种原因，您确实希望它从6开始，那么请清楚地说明决定结果集中应该出现哪些节点作为起点的规则。

我注意到样本数据中的每个ID都只有一个前身，但可能有多个后继。出于这个原因，我选择从标识端点的节点开始，然后返回到起点。希望下面的代码注释足以解释接下来发生的事情。

declare @TableX table (FromID int, ToID int);
insert @TableX values (1, 2), (2, 3), (3, 4), (5, 6), (6, 7), (6, 9), (9, 10);
with PathCTE as
(
    -- BASE CASE
    -- Any ID that appears as a "to" but not a "from" is the endpoint of a path. This
    -- query captures the ID that leads directly to that endpoint, plus the path
    -- represented by that one row in the table.
    select
        X1.FromID,
        [Path] = convert(varchar(max), X1.FromID) + ',' + convert(varchar(max), X1.ToID)
    from
        @TableX X1
    where
        not exists (select 1 from @TableX X2 where X2.FromID = X1.ToID)
    union all
    -- RECURSIVE CASE
    -- For every path previously identified, look for another record in @TableX that
    -- leads to its starting point and prepend that record's from ID to the overall path.
    select
        X.FromID,
        [Path] = convert(varchar(max), X.FromID) + ',' + PathCTE.[Path]
    from
        PathCTE
        inner join @TableX X on PathCTE.FromID = X.ToID
)
-- Any ID that appears as a "from" but not a "to" is the starting point of one or more
-- paths, so we get all results beginning at one of those points. All other output from
-- PathCTE is partial paths, which we can ignore.
select * 
from 
    PathCTE 
where
    not exists (select 1 from @TableX X where PathCTE.FromID = X.ToID)
order by
    FromID, [Path];

输出:

FromID   Path
1        1,2,3,4
5        5,6,7
5        5,6,9,10