好吧，假设“above”表示比current小的id，并且假设您需要最近城市id的新列-

SELECT      C.Id,
            C.City,
            ...,
            (
                SELECT      TOP(1)  C2.Id
                FROM        Cities  AS  C2
                WHERE       C2.Id   <   C.Id
                ORDER BY    ABS(C2.Latitude - C.Latitude)   --> put distance formula here
            )   AS          NearestCityId
            
FROM        Cities  AS  C
ORDER BY    C.Id

示例架构和数据：

create table cities 
(id int,
city varchar(100),
point geography);

insert into cities values (1, 'New York', 'POINT (-73.4 40.35)');
insert into cities values (2, 'Toronto', 'POINT (-79.34 43.65)');
insert into cities values (3, 'Singapore', 'POINT (103.85 1.29)');
insert into cities values (4, 'Tokyo', 'POINT (139.84 35.65)');
insert into cities values (5, 'Chicago', 'POINT (-87.62 41.88)');
insert into cities values (6, 'Milwaukee', 'POINT (-87.91 43.04)');

质询：

with cte as (
select
  c1.city as city1,
  c2.city as city2,
  c1.point.STDistance(c2.point) as dist
from cities c1
inner join cities c2
on c1.id > c2.id
),
cte2 as (
select
  row_number() over (partition by city1 order by dist asc) as row_number, 
  city1,
  city2,
  dist
from cte
)
select
  city1 as city,
  city2 as nearest_previous_city,
  dist
from cte2
where row_number = 1

具体步骤如下：

• cte计算给定城市与之前所有城市之间的距离（c1.id > c2.id）
• cte2按距离对每个城市的邻近城市排序，并给它们一个等级（partition by city1 order by dist asc））
• 对于具有前置任务的每个城市，cte3选择距离最近的城市（row_number = 1）