基于Haversine公式获取最近匹配并实现SQL Update的技术问询

我明白你的痛点：原SQL会生成所有失败配送（ROUTEID LIKE '%NDD%'）和有效新配送的组合，数据量爆炸，还要手动去重。咱们可以用窗口函数直接锁定每个失败配送的最近匹配，然后一步完成更新。

第一步：先验证最近匹配的结果 #

先写个查询确认每个失败配送对应的最近有效站点，这样你可以先核对数据是否正确：

WITH ValidDeliveryStops AS (
    -- 先把所有符合要求的新配送站点单独拎出来
    SELECT 
        lat AS latpoint,
        lon AS longpoint,
        RouteID,
        Sequence
    FROM tblOrderRouteStops WITH (NOLOCK)
    WHERE 
        CustID = 180016
        AND RouteID NOT LIKE '%NDD%'
        AND RouteID NOT LIKE '%PND%'
        AND RouteID NOT LIKE '%NFW%'
),
DistanceCalculations AS (
    -- 计算每个失败配送和所有有效站点的距离，同时给每个失败配送的匹配项按距离排名
    SELECT 
        ORS.PKID AS FailedStopID,
        ORS.Reference1,
        VDS.RouteID AS TargetRouteID,
        ROUND(
            ACOS(
                COS(RADIANS(90 - ORS.lat)) * COS(RADIANS(90 - VDS.latpoint)) +
                SIN(RADIANS(90 - ORS.lat)) * SIN(RADIANS(90 - VDS.latpoint)) * COS(RADIANS(ORS.lon - VDS.longpoint))
            ) * 3958.756,
            2
        ) AS Distance_in_mi,
        -- 关键：按失败配送分组，距离最近的排第1
        ROW_NUMBER() OVER (
            PARTITION BY ORS.PKID 
            ORDER BY ACOS(
                COS(RADIANS(90 - ORS.lat)) * COS(RADIANS(90 - VDS.latpoint)) +
                SIN(RADIANS(90 - ORS.lat)) * SIN(RADIANS(90 - VDS.latpoint)) * COS(RADIANS(ORS.lon - VDS.longpoint))
            ) ASC
        ) AS RankByDistance
    FROM tblOrderRouteStops ORS WITH (NOLOCK)
    CROSS JOIN ValidDeliveryStops VDS
    WHERE 
        ORS.CustID = 180016
        AND ORS.RouteID LIKE '%NDD%'
)
-- 只取每个失败配送排名第1的（最近的）匹配
SELECT 
    FailedStopID,
    Reference1,
    TargetRouteID,
    Distance_in_mi
FROM DistanceCalculations
WHERE RankByDistance = 1
ORDER BY FailedStopID;

第二步：转换成UPDATE语句更新RouteID #

确认上面的查询结果没问题后，就可以把这个逻辑改成UPDATE，直接把失败配送的RouteID替换成最近匹配的有效RouteID：

WITH ValidDeliveryStops AS (
    SELECT 
        lat AS latpoint,
        lon AS longpoint,
        RouteID
    FROM tblOrderRouteStops WITH (NOLOCK)
    WHERE 
        CustID = 180016
        AND RouteID NOT LIKE '%NDD%'
        AND RouteID NOT LIKE '%PND%'
        AND RouteID NOT LIKE '%NFW%'
),
NearestMatches AS (
    SELECT 
        ORS.PKID AS FailedStopID,
        VDS.RouteID AS TargetRouteID,
        ROW_NUMBER() OVER (
            PARTITION BY ORS.PKID 
            ORDER BY ACOS(
                COS(RADIANS(90 - ORS.lat)) * COS(RADIANS(90 - VDS.latpoint)) +
                SIN(RADIANS(90 - ORS.lat)) * SIN(RADIANS(90 - VDS.latpoint)) * COS(RADIANS(ORS.lon - VDS.longpoint))
            ) ASC
        ) AS RankByDistance
    FROM tblOrderRouteStops ORS WITH (NOLOCK)
    CROSS JOIN ValidDeliveryStops VDS
    WHERE 
        ORS.CustID = 180016
        AND ORS.RouteID LIKE '%NDD%'
)
UPDATE ORS
SET ORS.RouteID = NM.TargetRouteID
FROM tblOrderRouteStops ORS
INNER JOIN NearestMatches NM 
    ON ORS.PKID = NM.FailedStopID
WHERE 
    NM.RankByDistance = 1
    AND ORS.CustID = 180016
    AND ORS.RouteID LIKE '%NDD%';

几个重要的注意点 #

• 关于重复距离：如果有多个有效站点和某个失败配送的距离完全一样，ROW_NUMBER()会随机选一个。如果需要保留所有并列最近的，或者有其他规则，可以改成RANK()；
• 先查后更：一定要先运行第一步的查询确认匹配结果正确，再执行UPDATE，避免误改数据；
• NOLOCK提示：保留了你原SQL里的WITH (NOLOCK)，但要注意这个提示可能会读取未提交的脏数据，如果业务对数据一致性要求高，可以去掉；
• 性能优化：如果数据量很大（比如你说的5000个站点），可以考虑给CustID、RouteID、lat、lon这些字段加索引，提升计算速度。

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