MySQL：使用WHERE NOT EXISTS子句时如何保留邻近标记组中的一个

问题背景 #

在NodeJS地图应用里，需要清理彼此邻近的标记，但要求每组邻近标记中保留一个，确保每个区域都有标记留存。当前的SQL查询仅能保留完全无邻近标记的点，无法满足分组留一的需求。

原查询代码：

var diff = 1.7;

const query = `
SELECT m1.marker_id, m1.longitude, m1.latitude
FROM Marker m1
WHERE NOT EXISTS ( 
    SELECT * FROM Marker m2
    WHERE m1.marker_id != m2.marker_id
    AND m1.latitude <= m2.latitude + ${diff}
    AND m1.latitude >= m2.latitude - ${diff}
    AND m1.longitude <= m2.longitude + ${diff}
    AND m1.longitude >= m2.longitude - ${diff}
)
`;

Marker表结构：

CREATE TABLE `Marker` (
    `marker_id` int unsigned NOT NULL AUTO_INCREMENT, 
    `latitude` decimal(20,15) NOT NULL, 
    `longitude` decimal(20,15) NOT NULL,  
    PRIMARY KEY (`marker_id`),  
    UNIQUE KEY `id_UNIQUE` (`marker_id`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

解决方案 #

方法1：窗口函数实现（MySQL 8.0+推荐） #

利用ROW_NUMBER()窗口函数，将经纬度按diff分桶分组，每组保留一个标记：

var diff = 1.7;

const query = `
SELECT marker_id, longitude, latitude
FROM (
    SELECT 
        m.marker_id, 
        m.longitude, 
        m.latitude,
        ROW_NUMBER() OVER (
            PARTITION BY 
                FLOOR(m.latitude / ${diff}), 
                FLOOR(m.longitude / ${diff})
            ORDER BY m.marker_id ASC
        ) AS row_num
    FROM Marker m
) AS grouped_markers
WHERE row_num = 1;
`;

逻辑说明：

• FLOOR(m.latitude / ${diff})和FLOOR(m.longitude / ${diff})将经纬度按diff间隔划分成桶，同桶内的标记彼此距离不超过diff；
• ROW_NUMBER()为每个桶内的标记编号，取row_num=1即保留每组中marker_id最小的标记（可修改ORDER BY规则，比如按创建时间排序）。

方法2：自关联分组（兼容低版本MySQL） #

如果你的MySQL版本不支持窗口函数，用自关联筛选出每组中marker_id最小的标记：

var diff = 1.7;

const query = `
SELECT m1.marker_id, m1.longitude, m1.latitude
FROM Marker m1
LEFT JOIN Marker m2 
    ON m1.marker_id > m2.marker_id
    AND m1.latitude <= m2.latitude + ${diff}
    AND m1.latitude >= m2.latitude - ${diff}
    AND m1.longitude <= m2.longitude + ${diff}
    AND m1.longitude >= m2.longitude - ${diff}
WHERE m2.marker_id IS NULL;
`;

逻辑说明：

• 该查询筛选出没有比自身ID更小且在邻近范围内的标记的点，也就是每组邻近标记中ID最小的那个会被留存，其余被过滤。

补充提示 #

• 如果diff代表实际距离（如米），需用Haversine公式计算真实地理距离，避免经纬度度数在不同纬度实际长度不同的误差；
• 可根据业务需求调整保留标记的规则，比如选择每组中距离区域中心最近的标记，或最新创建的标记。

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