sql语句写算法，算出从A到C的最短距离？

 

DECLARE @T TABLE ([source] VARCHAR(1),direct VARCHAR(1),Mile INT)

INSERT INTO @T

SELECT 'A','B',20 UNION ALL

SELECT 'B','C',10 UNION ALL

SELECT 'C','D',15 UNION ALL

SELECT 'D','E',10 UNION ALL

SELECT 'E','F',5 UNION ALL

SELECT 'D','F',15 UNION ALL

SELECT 'B','D',20 UNION ALL

SELECT 'A','E',10 UNION ALL

SELECT 'A','F',15

 

--SQL查询如下:

 

;WITH Liang AS

(

    SELECT

        [source] as [from],direct AS [to],Mile

    FROM @T

    UNION ALL

    SELECT

        direct,[source],Mile

    FROM @T

),

Liang2 AS

(

    SELECT

        [from],[to],

        Mile AS totalMile,

        CAST('.'+[from]+'.'+[to]+'.' AS VARCHAR(MAX)) AS path

    FROM Liang

    UNION ALL

    SELECT

        A.[from],B.[to],

        A.totalMile+B.Mile,

        CAST(A.path+B.[to]+'.' AS VARCHAR(MAX))

    FROM Liang2 AS A

        JOIN Liang AS B

            ON CASE WHEN A.path LIKE '%.'+B.[to]+'.%'

                      THEN 1 ELSE 0 END=0

                AND A.[to]=B.[from]

),

Liang3 AS

(

    SELECT

        [from],[to],

        MIN(totalMile) AS MinMile

    FROM Liang2

    GROUP BY [from],[to]

)

SELECT B.*

FROM Liang3 AS A

  JOIN Liang2 AS B

      ON A.[from]=B.[from]

          AND A.[to]=B.[to]

          AND A.MinMile=B.totalMile

WHERE A.[from]='A'

    AND A.[to]='C'

OPTION(MAXRECURSION 0)