基于经纬度与邮编表，如何为所有用户匹配最近门店并计算距离？

原代码存在的问题 #

你提供的SQL代码无法实现需求，核心问题包括：

• 仅引用了单张表locations，未关联用户表和门店表
• 硬编码了固定纬度值37.423021，未使用用户的实际纬度数据
• 字段命名混乱（如store_lat、lat等不一致），未对应两张表的实际字段
• 仅对距离排序，未筛选出每个用户对应的最近门店

正确实现方案 #

假设：

• 门店表名为stores，字段：zipcode（邮编）、longitude（经度）、latitude（纬度）
• 用户表名为users，字段与门店表一致

我们使用Haversine公式计算球面距离（3959为英里制地球半径，若需公里制替换为6371），以下是两种可行方案：

方案1：窗口函数（推荐，适合大数据量）

通过窗口函数为每个用户的所有门店距离排序，取距离最近的门店：

WITH user_store_distances AS (
    SELECT 
        u.zipcode AS user_zipcode,
        s.zipcode AS store_zipcode,
        -- 计算英里距离，保留2位小数
        ROUND(
            3959 * ACOS(
                COS(RADIANS(u.latitude)) * COS(RADIANS(s.latitude)) * 
                COS(RADIANS(s.longitude) - RADIANS(u.longitude)) + 
                SIN(RADIANS(u.latitude)) * SIN(RADIANS(s.latitude))
            ),
            2
        ) AS distance_miles,
        -- 按用户分组，按距离升序排序，标记每条记录的位次
        ROW_NUMBER() OVER (PARTITION BY u.zipcode ORDER BY distance_miles ASC) AS rn
    FROM users u
    -- 关联所有用户和门店，计算两两距离
    CROSS JOIN stores s
    -- 可选：缩小经纬度范围，减少计算量（比如只查经度差1度、纬度差1度内的门店）
    -- WHERE ABS(u.longitude - s.longitude) < 1 AND ABS(u.latitude - s.latitude) < 1
)
-- 取每个用户的第一条记录（最近门店）
SELECT user_zipcode, store_zipcode, distance_miles
FROM user_store_distances
WHERE rn = 1;

方案2：子查询（适合小数据量）

通过子查询为每个用户单独查找最近门店：

SELECT 
    u.zipcode AS user_zipcode,
    -- 获取最近门店的邮编
    (SELECT s.zipcode
     FROM stores s
     ORDER BY 
         3959 * ACOS(
             COS(RADIANS(u.latitude)) * COS(RADIANS(s.latitude)) * 
             COS(RADIANS(s.longitude) - RADIANS(u.longitude)) + 
             SIN(RADIANS(u.latitude)) * SIN(RADIANS(s.latitude))
         ) ASC
     LIMIT 1) AS nearest_store_zipcode,
    -- 获取最近门店的距离，保留2位小数
    ROUND(
        (SELECT 
             3959 * ACOS(
                 COS(RADIANS(u.latitude)) * COS(RADIANS(s.latitude)) * 
                 COS(RADIANS(s.longitude) - RADIANS(u.longitude)) + 
                 SIN(RADIANS(u.latitude)) * SIN(RADIANS(s.latitude))
             )
         FROM stores s
         ORDER BY 
             3959 * ACOS(
                 COS(RADIANS(u.latitude)) * COS(RADIANS(s.latitude)) * 
                 COS(RADIANS(s.longitude) - RADIANS(u.longitude)) + 
                 SIN(RADIANS(u.latitude)) * SIN(RADIANS(s.latitude))
             ) ASC
         LIMIT 1),
        2
    ) AS distance_miles
FROM users u;

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