优化距离函数查询-SQLServer2005

Question

我写了一个函数来计算给定最大半径的2个点之间的距离。这个函数可以工作，但需要调用两次，就像我实现它的方式：

一次在选择块中，然后再次在where块中。

有更好的方法来实现这一点吗？

谢谢你的帮助！

select
    c.LABEL_NAME,
    k.*,
    dbo.kiw_GetNearMeetingLocationInMiles_FUNC(41.056466,-85.3312009,LATITUDE,LONGITUDE) as distance
    from USR_KIW_CUS_MEETING as k
    join CUSTOMER as c
    on c.MASTER_CUSTOMER_ID = k.MASTER_CUSTOMER_ID
    where c.USR_MEMBERSHIP_STATUS_CODE NOT IN('CR','CSD')
    and dbo.kiw_GetNearMeetingLocationInMiles_FUNC(41.056466,-85.3312009,LATITUDE,LONGITUDE)  <= 120;

更新

这是基于Mathieu Rodic 的建议

SELECT
    *
FROM
(
    SELECT
        c.LABEL_NAME,
        k.*,
        dbo.kiw_GetNearMeetingLocationInMiles_FUNC(41.056466,-85.3312009,LATITUDE,LONGITUDE) as distance
    FROM
        USR_KIW_CUS_MEETING as k
    JOIN
        CUSTOMER as c on c.MASTER_CUSTOMER_ID = k.MASTER_CUSTOMER_ID
    WHERE
        c.USR_MEMBERSHIP_STATUS_CODE NOT IN('CR','CSD')
)a
WHERE
   a.distance <= 120

Answer 1

尝试以下操作：

SELECT
    *
FROM
(
    SELECT
        c.LABEL_NAME,
        k.*,
        dbo.kiw_GetNearMeetingLocationInMiles_FUNC(41.056466,-85.3312009,LATITUDE,LONGITUDE) as distance
    FROM
        USR_KIW_CUS_MEETING as k
    JOIN
        CUSTOMER as c on c.MASTER_CUSTOMER_ID = k.MASTER_CUSTOMER_ID
    WHERE
        c.USR_MEMBERSHIP_STATUS_CODE NOT IN('CR','CSD')
) AS computed_table
WHERE
    distance <= 120

HAVING子句允许按计算列进行筛选。