迪杰斯特拉算法

IF OBJECT_ID('T_TEST') IS NOT NULL DROP TABLE T_TEST
GO
CREATE TABLE [dbo].[t_test](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,    --自增字段，无意义
    [header] [varchar](500),    --第一点的名称
    [next] [varchar](500),    --第二点的名称
    [distance] [int] NULL    --两点之间的距离
)
insert into t_test (header,next,distance) values ('A','B',20)
insert into t_test (header,next,distance) values ('A','C',10)
insert into t_test (header,next,distance) values ('B','D',30)
insert into t_test (header,next,distance) values ('D','E',40)
insert into t_test (header,next,distance) values ('C','E',50)
insert into t_test (header,next,distance) values ('D','F',30)
insert into t_test (header,next,distance) values ('E','G',70)
insert into t_test (header,next,distance) values ('F','H',100)
insert into t_test (header,next,distance) values ('G','H',340)
insert into t_test (header,next,distance) values ('H','I',420)
insert into t_test (header,next,distance) values ('I','J',15)
insert into t_test (header,next,distance) values ('J','K',70)
insert into t_test (header,next,distance) values ('K','L',60)
insert into t_test (header,next,distance) values ('L','M',100)
insert into t_test (header,next,distance) values ('J','N',45)
insert into t_test (header,next,distance) values ('N','O',85)
insert into t_test (header,next,distance) values ('M','O',65)
insert into t_test (header,next,distance) values ('O','P',90)
insert into t_test (header,next,distance) values ('P','Q',10)
insert into t_test (header,next,distance) values ('Q','R',20)
insert into t_test (header,next,distance) values ('P','S',50)
insert into t_test (header,next,distance) values ('Q','T',70)
insert into t_test (header,next,distance) values ('T','U',90)
insert into t_test (header,next,distance) values ('U','V',100)
insert into t_test (header,next,distance) values ('V','W',20)
insert into t_test (header,next,distance) values ('W','X',20)
insert into t_test (header,next,distance) values ('T','X',50)
insert into t_test (header,next,distance) values ('X','Y',70)
insert into t_test (header,next,distance) values ('Y','Z',90)

SELECT * FROM T_TEST

DECLARE @SOURCE VARCHAR(500),@TRAGET VARCHAR(500),@CITY VARCHAR(500)
--假设求A到Z的最短距离
SELECT @SOURCE='A',@TRAGET='Z'

--建立最短距离表
DECLARE @MINDISTANCE TABLE(
TRAGET [varchar](500)
,DISTANCE INT
)
--建立备选距离表
DECLARE @NOWDISTANCE TABLE(
TRAGET [VARCHAR](500)
,DISTANCE INT
)
--向备选距离表中插入初始记录
SELECT @SOURCE,0
UNION ALL
SELECT T1.header, T2.distance   FROM (
SELECT HEADER FROM t_test
UNION
SELECT NEXT FROM t_test
) T1
LEFT JOIN t_test T2 ON (T2.header=@SOURCE OR T2.next=@SOURCE )
AND (T2.header=T1.header OR T2.next=T1.header )
WHERE T1.HEADER<>@SOURCE
--从备选距离表中循环取最小距离插入最短距离表，同时更新备选距离表,直到目标地址出现在最短记录表中
WHILE NOT EXISTS(SELECT 1 FROM @MINDISTANCE WHERE TRAGET=@TRAGET )
BEGIN

SELECT TOP 1 @CITY=TRAGET  FROM @NOWDISTANCE T1 WHERE NOT EXISTS(
SELECT 1 FROM @NOWDISTANCE T2 WHERE T2.TRAGET<T1.TRAGET ) ORDER BY TRAGET

INSERT INTO @MINDISTANCE
SELECT TOP 1 * FROM @NOWDISTANCE T1 WHERE NOT EXISTS(
SELECT 1 FROM @NOWDISTANCE T2 WHERE T2.TRAGET<T1.TRAGET ) ORDER BY TRAGET

UPDATE @NOWDISTANCE SET DISTANCE=1
FROM @NOWDISTANCE T1
INNER JOIN t_test T2 ON (T1.TRAGET=T2.header OR T1.TRAGET=T2.next ) AND
END