求两地的最短距离

最新推荐文章于 2022-03-05 10:20:15 发布

htl258_Tony

最新推荐文章于 2022-03-05 10:20:15 发布

阅读量1.4k

点赞数

分类专栏：精华答题收集文章标签： insert table join c object 算法

精华答题收集专栏收录该内容

65 篇文章 0 订阅

订阅专栏

出发地目的地里程

source direct Mile
A B 20
B C 10
C D 15
D E 10
E F 5
D F 15
B D 20
A E 10
A F 15

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

---------------------------------
-- Author: liangCK 小梁
---------------------------------

--> 生成测试数据: @T
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)
/*
from to    totalMile path
---- ---- ---------- ----------------------------
A    C    30          .A.B.C.

(1 行受影响)

*/

--蓉儿 szx1999

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([source] varchar(1),[direct] varchar(1),[Mile] int)
insert [tb]
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
go
--select * from [tb]

with szx as
(
    select s=source,d=direct,t=mile,p=cast(source+'-'+direct as varchar(8000)) from tb where source='A'
    union all
    select a.s,b.direct,a.t+mile,p=a.p+'-'+b.direct
    from szx a join tb b
    on a.d=b.source and a.d<>'C' and charindex(b.direct,p)=0
)
select top 1 t as 最小距离,p as 路径 from szx where d='C' order by t
/*
最小距离        路径
----------- -----------------
30          A-B-C

(1 行受影响)
*/

------------------------------------------

--求最短乘车路线的例子
CREATE TABLE T_Line(
ID      nvarchar(10), --公交线路号
Station nvarchar(10), --站点名称
Orders int)           --行车方向(通过它反应每个站的上一个、下一个站)
INSERT T_Line
SELECT N'8路' ,N'站A',1 UNION ALL
SELECT N'8路' ,N'站B',2 UNION ALL
SELECT N'8路' ,N'站C',3 UNION ALL
SELECT N'8路' ,N'站D',4 UNION ALL
SELECT N'8路' ,N'站J',5 UNION ALL
SELECT N'8路' ,N'站L',6 UNION ALL
SELECT N'8路' ,N'站M',7 UNION ALL
SELECT N'20路' ,N'站G',1 UNION ALL
SELECT N'20路' ,N'站H',2 UNION ALL
SELECT N'20路' ,N'站I',3 UNION ALL
SELECT N'20路' ,N'站J',4 UNION ALL
SELECT N'20路' ,N'站L',5 UNION ALL
SELECT N'20路' ,N'站M',6 UNION ALL
SELECT N'255路',N'站N',1 UNION ALL
SELECT N'255路',N'站O',2 UNION ALL
SELECT N'255路',N'站P',3 UNION ALL
SELECT N'255路',N'站Q',4 UNION ALL
SELECT N'255路',N'站J',5 UNION ALL
SELECT N'255路',N'站D',6 UNION ALL
SELECT N'255路',N'站E',7 UNION ALL
SELECT N'255路',N'站F',8
GO

--乘车线路查询存储过程
CREATE PROC p_qry
@Station_Start nvarchar(10),
@Station_Stop nvarchar(10)
AS
SET NOCOUNT ON
DECLARE @l int
SET @l=0
SELECT ID,Station,
    Line=CAST('('+RTRIM(ID)+': '+RTRIM(Station) as nvarchar(4000)),
    Orders=Orders,
    [Level]=@l
INTO # FROM T_Line
WHERE Station=@Station_Start
WHILE @@ROWCOUNT>0
    AND NOT EXISTS(SELECT * FROM # WHERE Station=@Station_Stop)
BEGIN
    SET @l=@l+1
    INSERT #(Line,ID,Station,Orders,[Level])
    SELECT
        Line=a.Line+CASE
            WHEN a.ID=b.ID THEN N'->'+RTRIM(b.Station)
            ELSE N') ∝ ('+RTRIM(b.ID)
                +N': '+RTRIM(b.Station) END,
        b.ID,b.Station,b.Orders,@l
    FROM # a,T_Line b
    WHERE a.[Level]=@l-1
        AND(a.Station=b.Station AND a.ID<>b.ID
            OR a.ID=b.ID AND(
                a.Orders=b.Orders+1
                OR
                a.Orders=b.Orders-1))
        AND LEN(a.Line)<4000
        AND PATINDEX('%[ >]'+b.Station+'[-)]%',a.Line)=0
END
SELECT N'起点站'=@Station_Start
    ,N'终点站'=@Station_Stop
    ,N'乘车线路'=Line+N')'
FROM #
WHERE [Level]=@l
    AND Station=@Station_Stop
IF @@ROWCOUNT =0 --如果未有可以到达的线路,则显示处理结果表备查
    SELECT * FROM #
GO

--调用
EXEC p_qry N'站A',N'站L'
/*--结果
起点站终点站乘车线路
---------- ------------ -----------------------------------------------------------
站A    站L    (8路: 站A->站B->站C->站D->站J->站L)
--*/