SQL Server與最短路徑演算法

iSQlServer發表於2009-11-04
SQLServer演算法

　　題目:空間有若干個點，每個點之間的聯絡都是隨機的，現求任意一個點(設為A)到另一任意點(設為Z)之間間隔最少其他點的最佳演算法(可用SQL資料庫)

　　約束:在一個點中只可以直接找出和它有直接聯絡的點

　　用途:通過朋友列表以最快的速度認識一個認識的人(MM/GG)

　　比如5的好友列表中有1，30，3

　　7的好友列表中有9，5，8

　　10的好友列表中有7，21，30

　　11的好友列表中有7，5，30

　　21的好友列表中有7，30，66

　　30的好友列表中有21，88，99

　　如果5要和7交朋友，則可通過5-11-7。而5-30-21-7是較長的路徑。

　　各位大蝦有什麼絕招能在SQL裡實現這演算法?

　　--如果全部建立雙向關聯，可以試試看下面的語句


　　declare @t table
　　(
　　id int,
　　f_id varchar(20)
　　)
　　insert into @t
　　select 5,'1,7,30,3' union all
　　select 7,'11,21,9,5,8' union all
　　select 11,'7,21,30' union all
　　select 21,'7,11,30,66' union all
　　select 30,'5,11,21,88,99'
　　--select * from @t
　　declare @start int
　　declare @end int
　　declare @node int
　　declare @count int
　　declare @result varchar(100)
　　set @count=0
　　set @start=5
　　set @end=11
　　set @result=''
　　declare @tmp table
　　(
　　id int,
　　f_id varchar(20),

　　step int
　　)
　　insert into @tmp select @start,'',@count
　　while @end not in (select id from @tmp)
　　begin
　　set @count=@count+1
　　insert into @tmp
　　select distinct a.id,a.f_id,@count from @t a,@tmp b where charindex(rtrim(b.id),a.f_id)>0 and a.id not in (select id from @tmp)
　　end
　　select @result=rtrim(@count)+':'+rtrim(@end)
　　while @count>1
　　begin
　　set @count=@count-1
　　select top 1 @end=id from @tmp where step=@count and charindex(rtrim(@end),f_id)>0
　　select @result=rtrim(@count)+':'+rtrim(@end)+'/'+@result
　　end
　　select @result='0:'+rtrim(@start)+'/'+@result
　　select @result

　　/*

　　0:5/1:7/2:11

　　*/

　　點評：上面的方法的缺點是不能列出所有的路徑，只能列出最短路徑其中一條

　　5的列表中沒有7，是不是可以認為5不認識7，那麼5也不認識11，談何5-11-7是最短路徑?

　　--按照你說的邏輯，步驟如下

　　--1.建立查詢函式


　　CREATE　FUNCTION dbo.F_RouteSearch
　　(
　　@START INT,
　　@END INT
　　)
　　RETURNS VARCHAR(200)
　　AS
　　BEGIN
　　DECLARE @NODE INT
　　DECLARE @COUNT INT
　　DECLARE @RESULT VARCHAR(100)
　　SET @COUNT=0
　　SET @RESULT=''
　　DECLARE @TMP TABLE
　　(
　　ID INT,
　　F_ID VARCHAR(20),
　　STEP INT

　　)
　　INSERT INTO @TMP SELECT @START,(SELECT F_ID FROM LIST WHERE ID=@START),@COUNT
　　WHILE @END NOT IN (SELECT ID FROM @TMP)
　　BEGIN
　　SET @COUNT=@COUNT+1
　　INSERT INTO @TMP
　　SELECT DISTINCT a.ID,a.F_ID,@COUNT FROM List a,@TMP b WHERE CHARINDEX(','+RTRIM(a.ID)+',',','+b.F_ID+',')>0 and a.ID not in (SELECT ID FROM @TMP)
　　IF @@ROWCOUNT=0
　　BEGIN
　　SELECT @RESULT='NO ROUTE FIND'
　　GOTO RETURNHANDLE
　　END
　　END
　　SELECT @RESULT=RTRIM(@COUNT)+':'+RTRIM(@END)
　　WHILE @COUNT>1
　　BEGIN
　　SET @COUNT=@COUNT-1
　　SELECT TOP 1 @END=ID FROM @TMP WHERE STEP=@COUNT AND CHARINDEX(','+RTRIM(@END)+',',','+F_ID+',')>0
　　SELECT @RESULT=RTRIM(@COUNT)+':'+RTRIM(@END)+'→'+@RESULT
　　END
　　SELECT @RESULT='0:'+RTRIM(@START)+'→'+@RESULT
　　RETURNHANDLE:
　　RETURN @RESULT
　　END
　　GO

　　--準備測試資料(與LZ提供資料相同)


　　insert into list
　　select 5,'1,30,3' union all
　　select 7,'9,5,8' union all
　　select 10,'7,21,30' union all
　　select 11,'7,5,30' union all
　　select 21,'7,66,30' union all
　　select 30,'21,88,99'
　　go

　　--測試

　　select dbo.F_RouteSearch(5,7) --從5開始，到7為止

　　--結果

　　/*

　　0:5→1:30→2:21→3:7

　　註解

　　5通過30，21最後找到7，耗費3步完成

　　5不認識11，因此LZ所說的路徑5-11-7不成立

　　*/

　　--List表生成指令碼


　　CREATE TABLE [List] (
　　[id] [int] NULL ,
　　[f_id] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL
　　) ON [PRIMARY]
　　GO


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-618113/，如需轉載，請註明出處，否則將追究法律責任。

相關文章