主页 / dba / 问题 / 289727
Accepted
sql_girl
Asked: 2021-04-14 02:09:40 +0800 CST

选择两个方向城市之间的距离

  • 772

我有一个距离表和一个城市表。我以我现在拥有的方式加入了它们：cityA_id，city_A，cityB_id，cityB，以公里为单位的距离我需要获取cityA和CITY B之间的距离，然后在同一查询结果中获取cityB和CityA之间的距离。（意思是如果奥尔巴尼到奥本是 36 公里，我还想检查奥本到奥尔巴尼的距离（其中 cityA 将是奥本和 cityB 奥尔巴尼）样本数据： 在此处输入图像描述

我怎么能动人地写这个？

到目前为止我所拥有的：

with A as (
SELECT  [CITY_1],
[CITY_NAME]
,[CITY_2]

,[DIS_IN_KM]

FROM [DB].[dbo].[DISTANCE] d
join cities c
on d.CITY_1 = c.CITY_NO
),
first_direction as 
(
select A.[CITY_1] as cityA_id,A.[CITY_NAME] as [CityA],A.[CITY_2] as CityB_id,  c.CITY_NAME as CITYB, A.[DIS_IN_KM] as [distance in km],
c.CITY_NAME as CITYB2, A.[CITY_NAME] as CITYA2--, B.DIS_IN_KM as [distance in km opposite]

from A join CITIES c on A.CITY_2 = c.CITY_NO

)
select * from first_direction

表架构：

CREATE TABLE [dbo].[DISTANCE](
    [CITY_1] [char](6) NOT NULL,
    [CITY_2] [char](6) NOT NULL,
    [DRIVE_TIME] [decimal](9, 2) NOT NULL,
    [DIS_IN_KM] [smallint] NOT NULL,
    [HARD_DIST] [smallint] NOT NULL,
    [BUS_FARE] [decimal](9, 3) NOT NULL,
    [CODE1] [smallint] NOT NULL,
    [CODE2] [smallint] NOT NULL,
    [CODE3] [smallint] NOT NULL,
    [DATE] [datetime] NOT NULL,
    [TEMP1] [decimal](9, 3) NOT NULL,
    [TEMP2] [decimal](9, 3) NOT NULL,
    [TEMP3] [decimal](9, 3) NOT NULL,
 CONSTRAINT [PK_DISTANCE2] PRIMARY KEY NONCLUSTERED 
(
    [CITY_1] ASC,
    [CITY_2] ASC
)

CREATE TABLE [dbo].[CITIES](
    [CITY_NO] [char](6) NOT NULL,
    [CITY_NAME] [char](40) NOT NULL,
    [BASE_CODE] [tinyint] NOT NULL,
    [ZONE_CODE] [tinyint] NOT NULL,
    [NAME_2] [char](40) NOT NULL,
 CONSTRAINT [PK_CITIES_NUMBER] PRIMARY KEY NONCLUSTERED 
(
    [CITY_NO] ASC
)

提前致谢！

sql-server t-sql

1 个回答

  1. Best Answer

    我相信您的 CTE 已经为您提供了任何两个之间所有距离的列表Cities，因此您只需在反向字段上将 CTE 加入自身City即可获得反向结果，如下所示：

    WITH CityDistances AS 
(
    SELECT 
        CITYA_id,
        CITYA,
        CITYB_id,
        CityB,
        DIS_IN_KM
    FROM DB.dbo.DISTANCE d
    INNER JOIN cities c
        ON d.CITY_1 = c.CITY_NO
)

SELECT 
    CD1.CITYA_id,
    CD1.CITYA AS [starting point],
    CD1.CITYB_id,  
    CD1.CITYB as destination, 
    CD1.DIS_IN_KM AS [distance in km],
    CD2.CITYA_id AS [CITYA_id 2], -- Note you can call this CITYB_id instead if that's clearer to you (CD2.CITYA_id = CD1.CITYB_id)
    CD2.CITYA AS [starting point 2],
    CD2.CITYB_id AS [CITYB_id 2], -- Note you can call this CITYA_id instead if that's clearer to you (CD2.CITYB_id = CD1.CITYA_id)
    CD2.CITYB AS [destination 2], 
    CD2.DIS_IN_KM AS [distance in km 2],
FROM CityDistances AS CD1
INNER JOIN CityDistances CD2
    ON CD1.CITYA_id = CD2.CITYB_id
    AND CD1.CITYB_id = CD2.CITYA_id
    • 1

相关问题