選擇兩個方向城市之間的距離

我有一個距離表和一個城市表。我以我現在擁有的方式加入了它們：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
)

提前致謝！

我相信您的 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

引用自：https://dba.stackexchange.com/questions/289727