SQL Server:表的递归自连接(没有WITH子句)

Question

我有以下表格:

CREATE TABLE routes
(
    ID int identity not null primary key,
    from int,
    to int,
    length int not null
)

我需要从指定的ID1到ID2的所有路由。

我听说过WITH条款，但它根本不能让我满意。它不允许搜索从ID1到ID2的方法，因为它只返回FROM和TO值的表，很难搜索所有可能的方法。

我需要一些类似于递归内部连接的东西-例如:

SELECT * 
FROM routes r1
INNER JOIN routes r2 ON (r1.to = r2.from AND r2.to <> r1.from AND r1.from = ID1)

它让我寻找所有由2条路线组成的方法，从ID1开始，但不包括那些去某个地方然后回到ID1的方法。现在我可以选择所有在ID2上结束的路由(可能是NULL，但有可能出现一些东西)。如果有路线或路线在ID2结束，我想打破，如果没有，我想与路线r3内连接并重复过程，直到我找到一条路线。

我可以在程序和IF子句中做到这一点，但我认为一定有更好的解决方案。

正如我所说的，WITH子句不能满足我，因为它不能让我很容易地找到所有的路由，只有一条。

Answer 1

1. 我改变了一些东西，使它更容易玩。例如，避免[from]和[to]作为列名。
2. 我硬编码ID1和ID2，而不是使用变量。我不担心"长度"。"
3. 你会看到它找到迂回的路线，但不让他们重复任何步骤。
4. 未优化-只是显示如何工作。
5. 我有一些麻烦让代码格式化坚持。我将发布并尝试编辑。
6. 拨动底部的链接

CREATE TABLE routesID int/identity/not null主键,开始char(1)——int,完成char(1)——int——，[length] int not null)

GO
INSERT INTO routes VALUES 
  (1,'a','b')
 ,(2,'a','c')
 ,(3,'a','d')
 ,(4,'b','c')
 ,(5,'b','d')
 ,(6,'c','d')
 ,(7,'d','c')
GO
WITH cte AS (
 --anchor
 SELECT id
       ,start
       ,finish
       ,',' + CAST(id AS VARCHAR(MAX)) + ',' route_ids
   FROM routes
  WHERE start = 'a'
  UNION ALL
 --recursive part    
 SELECT a.id
       ,a.start
       ,b.finish
       ,route_ids + CAST(b.id AS VARCHAR(MAX)) + ','
   FROM cte a
        INNER JOIN
        routes b ON a.finish = b.start 
  WHERE CHARINDEX(',' + CAST(b.id AS VARCHAR(MAX)) + ',',a.route_ids,1)  = 0
)
SELECT start,finish,route_ids 
 FROM cte
WHERE finish = 'c'
ORDER BY LEN(route_ids)

小提琴