SQL查询同ID分组下空间距离最远的两个点的实现方法

问题根因 #

现有循环逻辑仅计算了id_point连续的相邻点位距离，没有遍历相同ID分组下所有点位的两两组合，因此无法得到同组内的最远点对。

正确实现方案 #

无需使用循环，通过自连接+窗口函数即可高效实现需求，代码如下：

WITH point_distance_pairs AS (
    SELECT
        a.ID,
        a.name,
        a.id_point AS start_point_id,
        b.id_point AS end_point_id,
        a.point.STDistance(b.point) AS distance,
        -- 同ID分组内按距离倒序排名
        ROW_NUMBER() OVER(PARTITION BY a.ID ORDER BY a.point.STDistance(b.point) DESC) AS rank_num
    FROM #points_on_lin a
    JOIN #points_on_lin b 
        ON a.ID = b.ID -- 限定同分组配对
        AND a.id_point < b.id_point -- 避免重复计算相同点对（如点2和3、3和2属于同一对，仅计算一次）
)
-- 取每个分组距离最大的点对
SELECT ID, name, start_point_id, end_point_id, distance
FROM point_distance_pairs
WHERE rank_num = 1

上述代码运行后，ID=1的分组会返回你需要的start_point_id=2、end_point_id=3的最远点对，ID=2的分组也会返回对应最远点对。如果需要将结果存入临时表，可在SELECT前添加INTO #points_dist即可。

内容的提问来源于stack exchange，提问作者Roman Schmidt