MySQL左连接结果限制：为客户位置匹配最多5个就近分支

嘿，这个需求我太熟了！之前帮朋友优化过类似的门店匹配逻辑，用MySQL的窗口函数就能轻松搞定每个客户位置只返回Top5就近分支的需求，下面给你两种方案，适配不同版本的MySQL：

方案一：MySQL 8.0+ 用窗口函数（推荐） #

窗口函数是最直观高效的方式，ROW_NUMBER() 可以帮我们给每个客户位置的分支按距离排序，然后筛选前5个。

先看完整的SQL示例：

WITH customer_branch_distances AS (
    SELECT
        c.location_id,
        c.company_id,
        c.lat AS customer_lat,
        c.lng AS customer_lng,
        b.branch_id,
        b.lat AS branch_lat,
        b.lng AS branch_lng,
        -- Haversine公式计算英里距离
        3956 * 2 * ASIN(SQRT(
            POWER(SIN((c.lat - b.lat) * PI()/180 / 2), 2) +
            COS(c.lat * PI()/180) * COS(b.lat * PI()/180) *
            POWER(SIN((c.lng - b.lng) * PI()/180 / 2), 2)
        )) AS distance
    FROM customers_locations c
    LEFT JOIN branches b ON
        -- 先过滤25英里范围内的分支，减少计算量
        3956 * 2 * ASIN(SQRT(
            POWER(SIN((c.lat - b.lat) * PI()/180 / 2), 2) +
            COS(c.lat * PI()/180) * COS(b.lat * PI()/180) *
            POWER(SIN((c.lng - b.lng) * PI()/180 / 2), 2)
        )) <= 25
    WHERE c.company_id = [你的指定company_id] -- 替换成目标ID
)
SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY location_id -- 按客户位置分组
            ORDER BY distance ASC -- 按距离从近到远排序
        ) AS rn
    FROM customer_branch_distances
) ranked
WHERE rn <= 5; -- 只保留每个客户的前5个分支

关键部分解释： #

• WITH 子句先计算所有符合25英里范围的客户-分支距离对，让逻辑更清晰
• ROW_NUMBER() OVER (PARTITION BY location_id ORDER BY distance ASC)：给每个客户位置的分支分配排名，距离最近的排第1
• 最后外层查询过滤rn <=5，确保每个客户最多返回5个分支

方案二：MySQL 5.x 用变量实现（兼容旧版本） #

如果你的MySQL版本还没到8.0，没法用窗口函数，可以用用户变量来模拟排名：

SELECT
    location_id,
    company_id,
    customer_lat,
    customer_lng,
    branch_id,
    branch_lat,
    branch_lng,
    distance
FROM (
    SELECT
        c.location_id,
        c.company_id,
        c.lat AS customer_lat,
        c.lng AS customer_lng,
        b.branch_id,
        b.lat AS branch_lat,
        b.lng AS branch_lng,
        3956 * 2 * ASIN(SQRT(
            POWER(SIN((c.lat - b.lat) * PI()/180 / 2), 2) +
            COS(c.lat * PI()/180) * COS(b.lat * PI()/180) *
            POWER(SIN((c.lng - b.lng) * PI()/180 / 2), 2)
        )) AS distance,
        -- 用变量记录当前客户位置，计算排名
        @rn := IF(@current_location = c.location_id, @rn + 1, 1) AS rn,
        @current_location := c.location_id
    FROM customers_locations c
    LEFT JOIN branches b ON
        3956 * 2 * ASIN(SQRT(
            POWER(SIN((c.lat - b.lat) * PI()/180 / 2), 2) +
            COS(c.lat * PI()/180) * COS(b.lat * PI()/180) *
            POWER(SIN((c.lng - b.lng) * PI()/180 / 2), 2)
        )) <= 25,
    -- 初始化变量
    (SELECT @current_location := NULL, @rn := 0) vars
    WHERE c.company_id = [你的指定company_id]
    ORDER BY c.location_id, distance ASC -- 必须按客户位置+距离排序
) ranked
WHERE rn <=5;

注意事项： #

• 这个方案里必须按location_id和distance排序，否则变量计算的排名会出错
• 变量初始化要放在FROM子句里，确保每次查询都从头开始计数

额外优化建议 #

• 既然lat和lng都有索引，可以考虑把Haversine的过滤条件改成先做一个粗略的范围过滤（比如按经纬度的大致差值筛选），再计算精确距离，能大幅提升查询速度
• 如果数据量很大，建议用MySQL的空间数据类型（比如POINT）和空间索引，用ST_Distance_Sphere函数来计算距离，比Haversine公式更高效

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