如何在许多行中的相同ID的SQL Server中的表上进行迭代？

我有一个叫的city表，一个叫的表routes_。它们之间的关系是多对多的，所以我map在它们之间使用了一个表。
routes_表具有路线的名称
city表具有城市的坐标。
map表格包含每个路线的所有城市及其路线顺序：

CREATE TABLE city
(
   ID int IDENTITY(1,1) PRIMARY KEY not null  , 
    name varchar(50) UNIQUE,
    populationOfCity int  not null,
    center char (10) not null,
    gover_id  int null,
    cordinates geography null,
);

CREATE TABLE routes_
(
    ID int IDENTITY(1,1) PRIMARY KEY not null,
    name varchar (50) not null,
);

CREATE TABLE map
(
    ID int IDENTITY(1,1) PRIMARY KEY not null  , 
    orderOfcity int not null,
    city_id int not null,
    route_id int not null,
    FOREIGN KEY( city_id )REFERENCES city (id),
    FOREIGN KEY( route_id )REFERENCES routes_ (id)
);

我需要找到最长的路线。我写这个算法来找到它，但实际上我对于sql server的语法是全新的，所以我需要一些帮助来编写它

1. 我使用地理类型使用来查找两个城市之间的距离stdDistance。
2. 我想group by按route_id到地图表，所以我将每条路线的城市分组
3. 通过排序地图表orederOfcity以计算距离
4. 对其进行分组和排序后，在地图表上进行迭代，并循环查找相同路线的每个路线的距离，
5. 将每个根的距离保存在数组中，然后找到最大值。

我开始写这篇文章，但我不知道如何完成循环语法

 SELECT map.route_id FROM map group by map.route_id order by MIN(map.orderOfcity)  

  declare @i int 
  declare @numOfRows int 
  set @i=1
  set @numOfRows=(SELECT COUNT (*) from map )
  while @i<@numOfRows

这是表格的插入语句：插入城市：

INSERT INTO city (name,populationOfCity,cordinates,center)
VALUES ('Damascus',3140000,geography::STGeomFromText('POINT(0.0275361 51.5064694)', 4326),'yes');


INSERT INTO city (name,populationOfCity,cordinates,center)
VALUES ('Alepo',3140000,geography::STGeomFromText('POINT(-66.1711278 -17.4125)', 4326),'Yes');

INSERT INTO city (name,populationOfCity,cordinates,center)
VALUES ('Hama',725000, geography::STGeomFromText('POINT(036.6666667 35.0833333)', 4326),'yes');

INSERT INTO city (name,populationOfCity,cordinates,center)
VALUES ('Homs',1033000, geography::STGeomFromText('POINT(036.7500000 34.6666667)', 4326),'yes');

INSERT INTO city (name,populationOfCity,cordinates,center)
VALUES ('Latakia',554000, geography::STGeomFromText('POINT(035.7500000 35.5000000)', 4326),'yes');

INSERT INTO city (name,populationOfCity,cordinates,center)
VALUES ('Tartus',393054, geography::STGeomFromText('POINT(035.9166667 34.9166667)', 4326),'yes');


INSERT INTO city (name,populationOfCity,cordinates,center,gover_id)
VALUES ('Al_Bab',20000, geography::STGeomFromText('POINT(037.4833333 36.3833333)', 4326),'No',2);

INSERT INTO city (name,populationOfCity,cordinates,center)
VALUES ('Afrin',24000, geography::STGeomFromText('POINT(036.8333333 36.5333333)', 4326),'No');


INSERT INTO city (name,populationOfCity,cordinates,center)
VALUES ('As Safirah',14000, geography::STGeomFromText('POINT(037.3500000 36.0833333)', 4326),'No');


INSERT INTO city (name,populationOfCity,cordinates,center)
VALUES ('Manbij',26000, geography::STGeomFromText('POINT(037.9500000 36.5166667)', 4326),'No');


INSERT INTO city (name,populationOfCity,cordinates,center)
VALUES ('Azaz',34000, geography::STGeomFromText('POINT(037.0666667   36.6000000)', 4326),'No');

插入路线：

INSERT INTO routes_(name) VALUES ('Al_Bab___Afrin');
INSERT INTO routes_(name) VALUES ('As Safirah___Azaz');
INSERT INTO routes_(name) VALUES ('Damascus___Latakia');
INSERT INTO routes_(name) VALUES ('Tartous___Damascus');

插入地图：

INSERT INTO map(orderOfcity,city_id,route_id) VALUES (1,7,1);
INSERT INTO map(orderOfcity,city_id,route_id) VALUES (2,9,1);
INSERT INTO map(orderOfcity,city_id,route_id) VALUES (3,8,1);

----As Safirah___Azaz
INSERT INTO map(orderOfcity,city_id,route_id) VALUES (1,9,2);
INSERT INTO map(orderOfcity,city_id,route_id) VALUES (2,8,2);
INSERT INTO map(orderOfcity,city_id,route_id) VALUES (3,10,2);
INSERT INTO map(orderOfcity,city_id,route_id) VALUES (4,11,2);



INSERT INTO map(orderOfcity,city_id,route_id) VALUES (1,6,4);
INSERT INTO map(orderOfcity,city_id,route_id) VALUES (2,4,4);
INSERT INTO map(orderOfcity,city_id,route_id) VALUES (3,6,4);

在我按route_id分组并按bu orderOfCity对其进行排序之后，迭代必须在地图的表格上，因此我知道每条路线的城市，然后我应该计算每组的距离，然后在地图上进行迭代表格，并根据具有相同route_id的城市之间的距离来计算城市之间的距离（如果路线中包含以下示例）：1）Al_Bab ___ Afrin 2）由于Safirah___Azaz在地图中的城市为：al bab，作为Safirah，Afrin，地图中的这些城市的路线ID为= 1，我应该计算它们之间的距离，然后移至mab中的下一个route_id为2并对其城市进行相同的计算。