问题:
我有以下(定向)图:
和这张 table :
CREATE TABLE [dbo].[T_Hops](
[UID] [uniqueidentifier] NULL,
[From] [nvarchar](1000) NULL,
[To] [nvarchar](1000) NULL,
[Distance] [decimal](18, 5) NULL
) ON [PRIMARY]
GO
内容如下:
INSERT INTO [dbo].[T_Hops] ([UID] ,[From] ,[To] ,[Distance]) VALUES (newid() ,'A' ,'E' ,10.00000 );
INSERT INTO [dbo].[T_Hops] ([UID] ,[From] ,[To] ,[Distance]) VALUES (newid() ,'E' ,'D' ,20.00000 );
INSERT INTO [dbo].[T_Hops] ([UID] ,[From] ,[To] ,[Distance]) VALUES (newid() ,'A' ,'B' ,5.00000 );
INSERT INTO [dbo].[T_Hops] ([UID] ,[From] ,[To] ,[Distance]) VALUES (newid() ,'B' ,'C' ,10.00000 );
INSERT INTO [dbo].[T_Hops] ([UID] ,[From] ,[To] ,[Distance]) VALUES (newid() ,'C' ,'D' ,5.00000 );
INSERT INTO [dbo].[T_Hops] ([UID] ,[From] ,[To] ,[Distance]) VALUES (newid() ,'A' ,'F' ,2.00000 );
INSERT INTO [dbo].[T_Hops] ([UID] ,[From] ,[To] ,[Distance]) VALUES (newid() ,'F' ,'G' ,6.00000 );
INSERT INTO [dbo].[T_Hops] ([UID] ,[From] ,[To] ,[Distance]) VALUES (newid() ,'G' ,'H' ,3.00000 );
INSERT INTO [dbo].[T_Hops] ([UID] ,[From] ,[To] ,[Distance]) VALUES (newid() ,'H' ,'D' ,1.00000 );
现在，我可以像这样查询从点x到点y的最佳连接:
WITH AllRoutes
(
[UID]
,[FROM]
,[To]
,[Distance]
,[Path]
,[Hops]
)
AS
(
SELECT
[UID]
,[FROM]
,[To]
,[Distance]
,CAST(([dbo].[T_Hops].[FROM] + [dbo].[T_Hops].[To]) AS varchar(MAX)) AS [Path]
,1 AS [Hops]
FROM [dbo].[T_Hops]
WHERE [FROM] = 'A'
UNION ALL
SELECT
[dbo].[T_Hops].[UID]
--,[dbo].[T_Hops].[FROM]
,Parent.[FROM]
,[dbo].[T_Hops].[To]
,CAST((Parent.[Distance] + [dbo].[T_Hops].[Distance]) AS [decimal](18, 5)) AS distance
,CAST((Parent.[Path] + '/' + [dbo].[T_Hops].[FROM] + [dbo].[T_Hops].[To]) AS varchar(MAX)) AS [Path]
,(Parent.[Hops] + 1) AS [Hops]
FROM [dbo].[T_Hops]
INNER JOIN AllRoutes AS Parent
ON Parent.[To] = [dbo].[T_Hops].[FROM]
)
SELECT TOP 100 PERCENT * FROM AllRoutes
/*
WHERE [FROM] = 'A'
AND [To] = 'D'
AND CHARINDEX('F', [Path]) != 0 -- via F
ORDER BY Hops, Distance ASC
*/
GO
现在，我想创建一个无向图，为此，例如
从D到A的路径
我从一个最简单的更改开始，然后向高清广告的相反方向发展。
INSERT INTO [dbo].[T_Hops]
([UID]
,[From]
,[To]
,[Distance])
VALUES
(newid() --<UID, uniqueidentifier,>
,'D' --<From, nvarchar(1000),>
,'H' --<To, nvarchar(1000),>
,1 --<Distance, decimal(18,5),>
)
GO
现在，正如预期的那样，我的查询引发了一个异常:
超过无限递归/最大递归级别(100)
因为现在可能的连接数是无限的。
现在，在Oracle中，您可以通过“按优先顺序连接”而不是树来执行相同的操作。
如果可能出现循环问题(无限递归)，则只需添加
按优先级连接的Nocycle，使其成为“按优先级连接的Nocycle”
现在在MS-SQL中，我通过添加以下内容修复了该行为:
AND Parent.[Path] NOT LIKE '%' + [dbo].[T_Hops].[FROM] + '/%'
到内部连接子句，本质上是模仿NOCYCLE。
但是，由于LIKE基本上是strstr(或更糟的strcasestr)，
因此，这比检查父元素数组要慢得多，
我非常担心表现。
毕竟，这只是一个例子，我打算基本上添加数据
整个国家
因此，最终结果可能会非常缓慢。
其他人还有一个更好的(=更快)的方法来替换MS SQL中的NOCYCLE吗？
还是这就是我别无选择，只能切换到Oracle(以可接受的速度执行此操作)的地方吗？
笔记:
任何临时表(大量数据)的解决方案都会变慢，
因为临时表将被交换到硬盘
当没有足够的RAM时(绝对确定)。
使用函数和表值函数的任何解决方案也是如此。
最佳答案
为了提高选择性能，在永久表中存储节点之间的可能路径
TABLE T_Hops_Path
(
FromNode,
ToNode,
HopCount,
TotalDistance
)
如果树结构不经常更改，则可以编写一个存储过程，每N小时生成一次此表。
关于sql - 图问题: connect by NOCYCLE prior replacement in SQL server?，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7105879/