SQL查询：如何计算两个城市之间的距离（基于预定义的表） [英] SQL Query : How to Calculate distance between two cities (based on predefined tables)

问题描述

您好，



我有一个包含许多表的数据库，特别是两个表一个存储路径，另一个存储路径的城市：



表路径[路径ID，名称]

表路由[ID，PathID（Forein Key），City，GoTime，BackTime，GoDistance，BackDistance]



表路径：



PathID .......名称

1 ..................纽约卡萨布兰卡阿尔法1

7 .................. .Paris Tokyo 6007 10：00



表路线：



ID ....... ... PATHID ..........市.................... GoTime .......... BACKTIME ... ....... GoDistance .......... BackDistance

1 ............ 1 ......... ...........纽约........... 08:00 ................. 23:46 .... ........... 5810 .................... NULL

2 ........ .... 1 ....................卡萨布兰卡...... 15点43 ............... ...... 16:03 ............... NULL .................... 5800

3 ............ 2 ....................巴黎........... ........ 10:20 .................. 14:01 ............... .. 3215 .................. NULL

4 ............ 2 ......... ...........开罗.................. 14:50 ................. .09：31 ............... 2425 .................... 3215

3 ............ 2 ....................迪拜................ 0.18：21 .................. 06:00 ............... NULL ......... ........... 2425



我想要一个Query，它在同一个Path中提供了所有可能的组合，例如：



PathID .......... CityFrom .......... CityTo ..........距离


我不知道我是否清楚自己，但希望你们能提前帮助我，而不是提前帮助我。

解决方案

  SELECT  
 r1.PathId，
 Go_Back =  CASE   WHEN  GoDistance  IS   NULL   THEN  ' 返回'  ELSE  '  Go'  END ，
 CityFrom = r1.City，
 CityTo = r1.City，
距离= ISNULL（GoDistance，BackDistance）
  FROM  
 dbo.Routes r1 
  INNER   JOIN  
 dbo.Routes r2 
  ON  
 r1.PathID = r2.PathID  AND  
 r1.City<> r2.City 

Hello,

I have a database with many tables, especially two tables one store paths and the other one store cities of a path :

Table Paths [ PathID, Name ]
Table Routes [ ID, PathID(Forein Key), City, GoTime, BackTime, GoDistance, BackDistance]

Table Paths :

PathID.......Name
1...................NewYork Casablanca Alpha 1
7...................Paris Tokyo 6007 10:00

Table Routes :

ID..........PathID..........City....................GoTime..........BackTime..........GoDistance..........BackDistance
1............1....................NewYork...........08:00.................23:46...............5810....................NULL
2............1....................Casablanca......15:43..................16:03...............NULL....................5800
3............2....................Paris...................10:20..................14:01...............3215....................NULL
4............2....................Cairo..................14:50..................09:31...............2425....................3215
3............2....................Dubai.................18:21..................06:00...............NULL....................2425

I want a Query that gives me all the possible combinations inside the same Path, something like :

PathID..........CityFrom..........CityTo..........Distance

I don't know if I made myself clear or not but hope you guys could help me, thanx in advance.

解决方案

SELECT
  r1.PathId,
  Go_Back = CASE WHEN GoDistance IS NULL THEN 'Back' ELSE 'Go' END,
  CityFrom = r1.City,
  CityTo = r1.City,
  Distance = ISNULL(GoDistance, BackDistance)
FROM
  dbo.Routes r1
    INNER JOIN
  dbo.Routes r2
      ON
        r1.PathID = r2.PathID AND
        r1.City <> r2.City

这篇关于SQL查询：如何计算两个城市之间的距离（基于预定义的表）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！