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屋!