SQL计算当前行与上方行的最小经纬度距离实现问询

不用SQL循环，SQL是集合型语言，用排序+自连接/横向连接就能高效实现需求，核心思路是先给数据按得分降序排好序号，限定只和序号更小（得分更高、位于上方）的行计算距离，再筛选出符合条件的记录。

步骤1：给行政区按得分降序排序（按城市分组） #

先给每个城市的行政区分配序号，得分最高的排第1（无上方行，会被保留），后续行序号递增：

WITH ranked_districts AS (
    SELECT 
        *,
        -- 按城市分组，同城市内按得分降序排号
        ROW_NUMBER() OVER (PARTITION BY city_name ORDER BY score DESC) AS row_num
    FROM city_data
)

步骤2：计算当前行与上方行的最小距离 #

根据你使用的SQL方言，选择以下两种高效实现方式：

方式一：用横向连接（LATERAL JOIN，推荐，性能更优） #

适用于PostgreSQL、MySQL 8.0+等支持LATERAL的数据库，对每行单独查询同城市内序号更小的上方行，计算最小距离：

WITH ranked_districts AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY city_name ORDER BY score DESC) AS row_num
    FROM city_data
)
SELECT 
    rd.*,
    sub.min_distance
FROM ranked_districts rd
LEFT JOIN LATERAL (
    -- 只查当前城市内、序号更小（得分更高）的上方行
    SELECT MIN(你的距离计算函数(rd.lat, rd.lng, rd_above.lat, rd_above.lng)) AS min_distance
    FROM ranked_districts rd_above
    WHERE rd_above.city_name = rd.city_name
      AND rd_above.row_num < rd.row_num
) sub ON true
-- 保留得分最高的行（min_distance为NULL），或与所有上方行距离>4000米的行
WHERE sub.min_distance > 4000 OR sub.min_distance IS NULL;

方式二：用自连接（兼容更多SQL方言） #

如果你的数据库不支持LATERAL，可以用自连接+分组聚合实现，注意数据量大时性能会稍差：

WITH ranked_districts AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY city_name ORDER BY score DESC) AS row_num
    FROM city_data
)
SELECT 
    rd.city_name,
    rd.district_name,
    rd.lat,
    rd.lng,
    rd.score,
    MIN(你的距离计算函数(rd.lat, rd.lng, rd_above.lat, rd_above.lng)) AS min_distance
FROM ranked_districts rd
LEFT JOIN ranked_districts rd_above 
    ON rd.city_name = rd_above.city_name
    AND rd.row_num > rd_above.row_num
GROUP BY rd.city_name, rd.district_name, rd.lat, rd.lng, rd.score, rd.row_num
HAVING MIN(你的距离计算函数(rd.lat, rd.lng, rd_above.lat, rd_above.lng)) > 4000 
    OR MIN(你的距离计算函数(rd.lat, rd.lng, rd_above.lat, rd_above.lng)) IS NULL;

关键说明 #

• 把代码中的你的距离计算函数替换成你已有的经纬度距离计算函数即可。
• 按city_name分区是为了限定只在同一城市内比较行政区距离，如果你需要全局比较（跨城市），去掉PARTITION BY city_name即可。
• 避免用循环（游标）：SQL循环是逐行处理，数据量大时性能极差，集合型操作才是SQL的最优解法。

内容的提问来源于stack exchange，提问作者T BBB