如何实现特定组仅1条结果的SQL周边搜索：连锁品牌留最近门店

要实现每个连锁组仅显示最近门店、保留所有非连锁商铺的需求，最简洁高效的方式是使用窗口函数（如ROW_NUMBER()），以下是具体实现：

核心思路 #

1. 先计算所有商铺到目标点的距离，并过滤出距离≤10的商铺
2. 对商铺进行分组排序：
   • 连锁商铺按chaingroup分组，组内按距离升序排序
   • 非连锁商铺（chaingroup为NULL）每个单独作为一组，确保全部保留
3. 只保留每个分组中排序为第1的商铺（即连锁组的最近门店、所有非连锁门店）

推荐SQL写法（支持窗口函数的数据库：MySQL 8.0+/PostgreSQL/SQL Server等） #

WITH shop_distances AS (
    SELECT 
        shop_id,
        shop,
        -- 计算到目标点(53.58393, 10.01413)的距离（单位：公里）
        ((ACOS(SIN(53.58393 * PI() / 180) * SIN(shop_lat * PI() / 180) + 
          COS(53.58393 * PI() / 180) * COS(shop_lat * PI() / 180) * 
          COS((10.01413 - shop_lon) * PI() / 180)) * 180 / PI()) * 
         60 * 1.150779448 * 1.609344) AS distance,
        chaingroup
    FROM shops
    -- 先过滤距离符合条件的商铺，减少后续计算量
    HAVING distance <= 10
)
SELECT shop, distance, chaingroup
FROM (
    SELECT 
        *,
        -- 按连锁组分段，非连锁商铺用shop_id作为唯一分段键
        ROW_NUMBER() OVER (
            PARTITION BY COALESCE(chaingroup, shop_id)
            ORDER BY distance ASC
        ) AS rn
    FROM shop_distances
) ranked_shops
-- 只保留每个分段的第1条（最近门店/非连锁门店）
WHERE rn = 1
ORDER BY distance ASC;

代码说明 #

• COALESCE(chaingroup, shop_id)：处理非连锁商铺的分组逻辑，因为NULL不等于任何值，用shop_id作为唯一分区键，确保每个非连锁商铺都能被保留
• ROW_NUMBER()：为每个分组内的商铺按距离排序，标记序号rn，取rn=1即可得到每个连锁组的最近门店
• CTE（WITH子句）：将距离计算逻辑单独提取，提升代码可读性

兼容老版本数据库的写法（无窗口函数支持） #

如果你的数据库不支持窗口函数（如MySQL 5.x），可以使用关联子查询实现：

SELECT 
    s.shop,
    -- 计算距离
    ((ACOS(SIN(53.58393 * PI() / 180) * SIN(s.shop_lat * PI() / 180) + 
      COS(53.58393 * PI() / 180) * COS(s.shop_lat * PI() / 180) * 
      COS((10.01413 - s.shop_lon) * PI() / 180)) * 180 / PI()) * 
     60 * 1.150779448 * 1.609344) AS distance,
    s.chaingroup
FROM shops s
WHERE 
    -- 过滤距离≤10的商铺
    ((ACOS(SIN(53.58393 * PI() / 180) * SIN(s.shop_lat * PI() / 180) + 
      COS(53.58393 * PI() / 180) * COS(s.shop_lat * PI() / 180) * 
      COS((10.01413 - s.shop_lon) * PI() / 180)) * 180 / PI()) * 
     60 * 1.150779448 * 1.609344) <= 10
    -- 保留非连锁商铺，或连锁组中没有更近的商铺
    AND (
        s.chaingroup IS NULL
        OR NOT EXISTS (
            SELECT 1
            FROM shops s2
            WHERE s2.chaingroup = s.chaingroup
                -- 存在同组且距离更近的商铺则排除当前商铺
                AND ((ACOS(SIN(53.58393 * PI() / 180) * SIN(s2.shop_lat * PI() / 180) + 
                      COS(53.58393 * PI() / 180) * COS(s2.shop_lat * PI() / 180) * 
                      COS((10.01413 - s2.shop_lon) * PI() / 180)) * 180 / PI()) * 
                     60 * 1.150779448 * 1.609344) < 
                    ((ACOS(SIN(53.58393 * PI() / 180) * SIN(s.shop_lat * PI() / 180) + 
                      COS(53.58393 * PI() / 180) * COS(s.shop_lat * PI() / 180) * 
                      COS((10.01413 - s.shop_lon) * PI() / 180)) * 180 / PI()) * 
                     60 * 1.150779448 * 1.609344)
        )
    )
ORDER BY distance ASC;

注意事项 #

• 老版本写法需要重复计算距离，性能不如窗口函数方案，优先推荐使用窗口函数
• 如果数据量较大，建议给chaingroup、shop_lat、shop_lon建立索引，提升查询效率

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