求最短路徑

Andy-W 2012-12-04 10:08:13

如圖:


SQL:

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
create nonclustered index IX_RelactionGraph_Source on RelactionGraph(Source) include(Destination)
create nonclustered index IX_RelactionGraph_Destination on RelactionGraph(Destination) include(Source)
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')

go




問題:
------------------------------------------
如何計算出"p"到'l'線路中,哪一條線路經過的節點最少?爲什麽?



...全文
271 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
vv75251455 2012-12-06
  • 打赏
  • 举报
回复
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 行受影响)
Andy-W 2012-12-06
  • 打赏
  • 举报
回复
下面SQL代码是采用双源点的BFS算法,一旦找到切点就结束搜索,返回最短路径:


--Procedure:
if object_id('up_GetPath') Is not null
	Drop proc up_GetPath
go
create proc up_GetPath
(
	@Source nvarchar(50),
	@Destination nvarchar(50)
)
As
set nocount on

declare 
	@level smallint =1, --当前搜索的深度
	@MaxLevel smallint=100, --最大可搜索深度
	@Source_WhileFlag bit=1, --以@Source作为中心进行搜索时候,作为能否循环搜索的标记
	@Destination_WhileFlag bit=1 --以@Destination作为中心进行搜索时候,作为能否循环搜索的标记

--如果直接找到两个Source存在直接关系就直接返回
if Exists(select 1 from RelactionGraph where (Source=@Source And Destination=@Destination) or (Source=@Destination And Destination=@Source) ) or @Source=@Destination
begin
	select convert(nvarchar(2000),@Source + ' --> '+ @Destination) As RelactionGraphPath,convert(smallint,0) As StopCount
	return
end

--

if object_id('tempdb..#1') Is not null Drop Table #1 --临时表#1,存储的是以@Source作为中心向外扩展的各节点数据
if object_id('tempdb..#2') Is not null Drop Table #2 --临时表#2,存储的是以@Destination作为中心向外扩展的各节点数据

create table #1(
	Source nvarchar(50),--相对源点
	Destination nvarchar(50), --相对目标
	Level smallint --深度
	) 
	
create table #2(Source nvarchar(50),Destination nvarchar(50),Level smallint) 

insert into #1 ( Source, Destination, Level )
	select Source, Destination, @level from RelactionGraph a where a.Source =@Source union --正向:以@Source作为源查询
	select Destination, Source, @level from RelactionGraph a where a.Destination = @Source --反向:以@Source作为目标进行查询
set @Source_WhileFlag=sign(@@rowcount)
	
insert into #2 ( Source, Destination, Level )
	select Source, Destination, @level from RelactionGraph a where a.Source =@Destination union --正向:以@Destination作为源查询
	select Destination, Source, @level from RelactionGraph a where a.Destination = @Destination --反向:以@Destination作为目标进行查询
set @Destination_WhileFlag=sign(@@rowcount)

--如果在表RelactionGraph中找不到@Source 或 @Destination 数据,就直接跳过后面的While过程
if not exists(select 1 from #1) or not exists(select 1 from #2)
begin
	goto While_Out
end


while not exists(select 1 from #1 a inner join #2 b on b.Destination=a.Destination) --判断是否出现切点
	 and (@Source_WhileFlag|@Destination_WhileFlag)>0 --判断是否能搜索
	 And @level<@MaxLevel --控制深度
begin
	if @Source_WhileFlag >0 
	begin	
		insert into #1 ( Source, Destination, Level )
			--正向
			select a.Source,a.Destination,@level+1
				From RelactionGraph a
				where exists(select 1 from #1 where Destination=a.Source And Level=@level) And
					Not exists(select 1 from #1 where Source=a.Source)			
			union
			--反向
			select a.Destination,a.Source,@level+1
				From RelactionGraph a
				where exists(select 1 from #1 where Destination=a.Destination And Level=@level) And
					Not exists(select 1 from #1 where Source=a.Destination)
		
		set @Source_WhileFlag=sign(@@rowcount)

	end
	
	
	if @Destination_WhileFlag >0
	begin		
		insert into #2 ( Source, Destination, Level )
			--正向
			select a.Source,a.Destination,@level+1
				From RelactionGraph a
				where exists(select 1 from #2 where Destination=a.Source And Level=@level) And
					Not exists(select 1 from #2 where Source=a.Source)
			union
			--反向
			select a.Destination,a.Source,@level+1
				From RelactionGraph a
				where exists(select 1 from #2 where Destination=a.Destination And Level=@level) And
					Not exists(select 1 from #2 where Source=a.Destination)
		set @Destination_WhileFlag=sign(@@rowcount)
	end
	
	select @level+=1
end

While_Out: 

--下面是构造返回的结果路径
if object_id('tempdb..#Path1') Is not null Drop Table #Path1
if object_id('tempdb..#Path2') Is not null Drop Table #Path2

;with cte_path1 As
(
select a.Source,a.Destination,Level,convert(nvarchar(2000),a.Source+' -> '+a.Destination) As RelactionGraphPath,Convert(smallint,1) As PathLevel From #1 a where exists(select 1 from #2 where Destination=a.Destination)
union all
select b.Source,a.Destination,b.Level,convert(nvarchar(2000),b.Source+' -> '+a.RelactionGraphPath) As RelactionGraphPath ,Convert(smallint,a.PathLevel+1) As PathLevel 
	from cte_path1 a
		inner join #1 b on b.Destination=a.Source
			and b.Level=a.Level-1
)
select *  Into #Path1 from cte_path1

;with cte_path2 As
(
select a.Source,a.Destination,Level,convert(nvarchar(2000),a.Source) As RelactionGraphPath,Convert(smallint,1) As PathLevel From #2 a where exists(select 1 from #1 where Destination=a.Destination)
union all
select b.Source,a.Destination,b.Level,convert(nvarchar(2000),a.RelactionGraphPath+' -> '+b.Source) As RelactionGraphPath ,Convert(smallint,a.PathLevel+1)
	from cte_path2 a
		inner join #2 b on b.Destination=a.Source
			and b.Level=a.Level-1
)
select *  Into #Path2 from cte_path2

;with cte_result As
(
select a.RelactionGraphPath+' -> '+b.RelactionGraphPath As RelactionGraphPath,a.PathLevel+b.PathLevel -1 As StopCount,rank() over(order by a.PathLevel+b.PathLevel) As Result_row
	From #Path1 a
		inner join #Path2 b on b.Destination=a.Destination
			and b.Level=1
	where a.Level=1
)	
select distinct RelactionGraphPath,StopCount From cte_result where Result_row=1
go



测试:

use test
go
exec dbo.up_GetPath 
		@Source = 'p', -- nvarchar(50)
        @Destination = 'l' -- nvarchar(50)
exec dbo.up_GetPath 
		@Source = 'p', -- nvarchar(50)
        @Destination = 'j' -- nvarchar(50)
exec dbo.up_GetPath 
		@Source = 'a', -- nvarchar(50)
        @Destination = 'o' -- nvarchar(50)

RelactionGraphPath StopCount --------------------------------- p -> k -> f -> l 2 p -> k -> o -> l 2 RelactionGraphPath StopCount --------------------------------- p -> k -> o -> i -> c -> j 4 RelactionGraphPath StopCount --------------------------------- a -> c -> i -> o 2
我腫了 2012-12-04
  • 打赏
  • 举报
回复
USE test
go

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
create nonclustered index IX_RelactionGraph_Source on RelactionGraph(Source) include(Destination)
create nonclustered index IX_RelactionGraph_Destination on RelactionGraph(Destination) include(Source)
go
 
insert into RelactionGraph (Source, Destination ) values ('a','b')
insert into RelactionGraph (Source, Destination ) values ('a','c')
insert into RelactionGraph (Source, Destination ) values ('a','d')
insert into RelactionGraph (Source, Destination ) values ('a','e')
insert into RelactionGraph (Source, Destination ) values ('b','f')
insert into RelactionGraph (Source, Destination ) values ('b','g')
insert into RelactionGraph (Source, Destination ) values ('b','h')
insert into RelactionGraph (Source, Destination ) values ('c','i')
insert into RelactionGraph (Source, Destination ) values ('c','j')
insert into RelactionGraph (Source, Destination ) values ('f','k')
insert into RelactionGraph (Source, Destination ) values ('f','l')
insert into RelactionGraph (Source, Destination ) values ('k','o')
insert into RelactionGraph (Source, Destination ) values ('k','p')
insert into RelactionGraph (Source, Destination ) values ('o','i')
insert into RelactionGraph (Source, Destination ) values ('o','l')
 
GO	

DECLARE @startPoint NVARCHAR(20),@endPoint NVARCHAR(20)

SELECT	@startPoint=N'p'
		,@endPoint=N'l'
		
		
IF object_id('tempdb..#Result')IS NOT NULL
	DROP TABLE #Result
;WITH c1 AS(
	SELECT 
			Source					AS startPoint
			,Destination				AS ENDPOINT
			,Source+','+Destination AS Route 
		FROM RelactionGraph
	UNION
	SELECT 
			Destination
			,Source
			,Destination +','+Source 
		FROM RelactionGraph
)
,c2 AS (
	SELECT 
			0 AS Row
			,startPoint
			,endPoint
			,CONVERT(NVARCHAR(max),Route) AS Route
		FROM c1
	UNION ALL
	SELECT 
			b.Row+1
			,b.startPoint
			,a.endPoint
			,CONVERT(NVARCHAR(MAX),b.Route+','+a.endPoint)
		FROM c1				AS a
			INNER JOIN c2	AS b ON a.startPoint=b.endPoint
		WHERE CHARINDEX(a.endPoint,b.Route)=0
)
SELECT 
		Row+2		AS 步數
		,startPoint AS 始點
		,endPoint	AS 終點
		,Route		AS 路線 
	INTO #Result
	FROM c2
	WHERE startPoint=@startPoint 
		AND endPoint=@endPoint
	ORDER BY 步數
	OPTION (MAXRECURSION 0)


SELECT	
		a.步數
		,a.始點
		,a.終點
		,a.路線
		,CONVERT(BIT,CASE WHEN NOT EXISTS(SELECT 1 FROM #Result AS x
									WHERE x.步數<a.步數
						)
				THEN 1
			ELSE 0
		END) AS 是否最佳路線
	FROM #Result AS a

/*
步數  始點  終點  路線					是否最佳路線
----- ----- ----- ---------------------	-------------
4     p     l     p,k,o,l				1
4     p     l     p,k,f,l				1
9     p     l     p,k,f,b,a,c,i,o,l		0
9     p     l     p,k,o,i,c,a,b,f,l		0
*/										
我腫了 2012-12-04
  • 打赏
  • 举报
回复
/*
步數  始點  終點  路線
----- ----- ----- ------------------
4     p     l     p,k,o,l
4     p     l     p,k,f,l
9     p     l     p,k,f,b,a,c,i,o,l
9     p     l     p,k,o,i,c,a,b,f,l
*/
我腫了 2012-12-04
  • 打赏
  • 举报
回复
USE test
go

--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
--create nonclustered index IX_RelactionGraph_Source on RelactionGraph(Source) include(Destination)
--create nonclustered index IX_RelactionGraph_Destination on RelactionGraph(Destination) include(Source)
--go
-- 
--insert into RelactionGraph (Source, Destination ) values ('a','b')
--insert into RelactionGraph (Source, Destination ) values ('a','c')
--insert into RelactionGraph (Source, Destination ) values ('a','d')
--insert into RelactionGraph (Source, Destination ) values ('a','e')
--insert into RelactionGraph (Source, Destination ) values ('b','f')
--insert into RelactionGraph (Source, Destination ) values ('b','g')
--insert into RelactionGraph (Source, Destination ) values ('b','h')
--insert into RelactionGraph (Source, Destination ) values ('c','i')
--insert into RelactionGraph (Source, Destination ) values ('c','j')
--insert into RelactionGraph (Source, Destination ) values ('f','k')
--insert into RelactionGraph (Source, Destination ) values ('f','l')
--insert into RelactionGraph (Source, Destination ) values ('k','o')
--insert into RelactionGraph (Source, Destination ) values ('k','p')
--insert into RelactionGraph (Source, Destination ) values ('o','i')
--insert into RelactionGraph (Source, Destination ) values ('o','l')
-- 
--GO	

DECLARE @startPoint NVARCHAR(20),@endPoint NVARCHAR(20)

SELECT	@startPoint=N'p'
		,@endPoint=N'l'
		
		

;WITH c1 AS(
	SELECT a.Source AS startPoint,a.Destination AS endPoint,a.Source+','+a.Destination AS Route FROM RelactionGraph AS a
	UNION
	SELECT DISTINCT a.Destination,a.Source,a.Destination +','+a.Source FROM RelactionGraph AS a
)
,c2 AS (
	SELECT 
			0 AS Row
			,a.startPoint
			,a.endPoint
			,CONVERT(NVARCHAR(max),a.Route) AS Route
		FROM c1 AS a
	UNION ALL
	SELECT 
			b.Row+1
			,CASE WHEN a.startPoint=b.endPoint THEN b.startPoint ELSE a.startPoint END
			,CASE WHEN a.startPoint=b.endPoint THEN a.endPoint ELSE b.endPoint END
			,CONVERT(NVARCHAR(MAX),b.Route+','+a.endPoint)
		FROM c1				AS a
			INNER JOIN c2	AS b ON a.startPoint=b.endPoint
		WHERE NOT(CHARINDEX(a.startPoint,b.Route)>0 AND CHARINDEX(a.endPoint,b.Route)>0)
)
SELECT Row+2 AS 步數,startPoint AS 始點,endPoint AS 終點,Route AS 路線  FROM c2
WHERE startPoint=@startPoint AND endPoint=@endPoint
ORDER BY 步數
OPTION (MAXRECURSION 0)
三断笛 2012-12-04
  • 打赏
  • 举报
回复
引用 10 楼 xxyj6450 的回复:
2楼高手. SQL 2005/2008 技术内幕 最后有讲树的处理,包括最短路径.
错啦,我看到的是6楼,不过相信2楼也是高手.
三断笛 2012-12-04
  • 打赏
  • 举报
回复
2楼高手. SQL 2005/2008 技术内幕 最后有讲树的处理,包括最短路径.
Andy-W 2012-12-04
  • 打赏
  • 举报
回复
引用 4 楼 ssp2009 的回复:
你图上的路径尖头表明p到不了i


Source 和 Destination 相對來說的的,可以從Source To Destination ,也可以Destination to Source.

快溜 2012-12-04
  • 打赏
  • 举报
回复
你图上的路径尖头表明p到不了i
haitao 2012-12-04
  • 打赏
  • 举报
回复
这种任务,规模大了,很难完全遍历,而需要很多额外处理、排除 应该是传统语言、客户端程序所擅长的,sql并不适合
newtee 2012-12-04
  • 打赏
  • 举报
回复
不会写 CTE应该可以
Andy-W 2012-12-04
  • 打赏
  • 举报
回复
補充:Source 和 Destination 相對來說的的,可以從Source To Destination ,也可以Destination to Source.
Andy-W 2012-12-04
  • 打赏
  • 举报
回复
等2天

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧