22,206
社区成员
发帖
与我相关
我的任务
分享
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
--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
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
*/
/*
步數 始點 終點 路線
----- ----- ----- ------------------
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
*/
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)