求最短路

如：


SQL:

不会写 CTE应该可以
[解决办法]
这种任务，规模大了，很难完全遍历，而需要很多额外处理、排除
应该是传统语言、客户端程序所擅长的，sql并不适合
[解决办法]
你图上的路径尖头表明p到不了i
[解决办法]
if object_id('RelactionGraph') Is not null 
drop table RelactionGraph 
create table RelactionGraph
(ID int  identity,Source nvarchar(50),Destination nvarchar(20),constraint PK_RelactionGraph primary key(ID))
go 
insert into RelactionGraph (Source, Destination ) values    ('a','b'),('a','c'),('a','d'),('a','e'),     ('b','f'),('b','g'),('b','h'),     ('c','i'),('c','j'),     ('f','k'),('f','l'),     ('k','o'),('k','p'),     ('o','i'),('o','l')  

;WITH ww AS(
SELECT CAST('p' AS VARCHAR(10))[1],
CAST((CASE Source WHEN 'p' THEN Destination ELSE Source END) AS VARCHAR(10))[2],
'p'+'->'+CAST((CASE Source WHEN 'p' THEN Destination ELSE Source END) AS VARCHAR(max)) [3] 
FROM RelactionGraph
WHERE Source ='p' OR Destination ='p'
UNION ALL 
SELECT  CAST([2] AS VARCHAR(10)),
CAST((CASE Source WHEN [2] THEN Destination ELSE Source END)  AS VARCHAR(10)),
[3]+'->'+CAST((CASE Source WHEN [2] THEN Destination ELSE Source END)  AS VARCHAR(10))
FROM ww a
JOIN RelactionGraph b
ON (Source =[2] OR Destination =[2] )AND (CASE Source WHEN [2] THEN Destination ELSE Source END)<>[1]
)
SELECT TOP 1 [3] [ ]  
FROM ww
WHERE [2]='i'



-------------------
p->k->o->i

(1 行受影响)