sqlite选择分组结果的最小函数值

发布于 2024-10-05 09:31:00 字数 397 浏览 5 评论 0原文

我有一张包含连锁餐厅 GPS 位置的表，想返回在一定半径内最接近点 (A) 的餐厅的地址

SELECT *
    , MIN(distance($lat, $lon, lat, lon)) as miles 
FROM all_restaurants 
WHERE lat between $lat1 and $lat2 
    AND lon between $lon1 and $lon2 
    AND miles < $miles 
GROUP BY restaurant_id 
ORDER BY miles ASC
    , company_name ASC 
LIMIT 500

返回错误

滥用聚合：MIN()

原文

I have a table with gps locations of restaurants chains and would like to return the addresses of the restaurants closest to point (A) within a certain radius

SELECT *
    , MIN(distance($lat, $lon, lat, lon)) as miles 
FROM all_restaurants 
WHERE lat between $lat1 and $lat2 
    AND lon between $lon1 and $lon2 
    AND miles < $miles 
GROUP BY restaurant_id 
ORDER BY miles ASC
    , company_name ASC 
LIMIT 500

returns error

misuse of aggregate: MIN()

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

翻身的咸鱼 2024-10-12 09:31:00

我认为您的问题是您仅按 restaurant_id 进行分组。当您使用 MIN、MAX、SUM、AVG 等聚合函数时，您需要在 SELECT 语句中包含不在任何聚合函数中的每一列。在这种情况下，您有两个选择，要么在 SELECT 中仅将 restaurant_id 如下所示：

SELECT restaurant_id 
        , MIN(distance($lat, $lon, lat, lon)) as miles 
    FROM all_restaurants 
    WHERE lat between $lat1 and $lat2 
        AND lon between $lon1 and $lon2 
        AND miles < $miles 
    GROUP BY restaurant_id 
    ORDER BY miles ASC
        , company_name ASC 
    LIMIT 500

或者将“*”中包含的所有其他列放在分组中 (因为您不能使用GROUP BY *）。

I think that your problem is that you only are grouping by restaurant_id. When you use an aggregation function as MIN, MAX, SUM, AVG, etc, you need to include every column in the SELECT statement that are not in any aggregation function. In this case you have two options, either in the SELECT you only put restaurant_id as the following:

SELECT restaurant_id 
        , MIN(distance($lat, $lon, lat, lon)) as miles 
    FROM all_restaurants 
    WHERE lat between $lat1 and $lat2 
        AND lon between $lon1 and $lon2 
        AND miles < $miles 
    GROUP BY restaurant_id 
    ORDER BY miles ASC
        , company_name ASC 
    LIMIT 500

Or you put every other column included in the '*' on the grouping (because you can't use GROUP BY *).

回复收藏 0 原文

热风软妹 2024-10-12 09:31:00

我敢打赌，您正在调用一个函数 distance($lat, $lon, lat, lon)，不是吗？

您是否尝试过在 $lat 和 lat 之间使用简单的减法，并使用 MIN 函数返回最小值？

我猜，MIN 的用途是与表字段或一些基本操作（例如减法）一起使用。这可能是遇到错误的原因。

最重要的是，在使用聚合函数时避免使用 *，列出查询中所需的列。

也许向我们提供示例数据可能会帮助我们找到解决您问题的方法。

回复收藏 0 原文

梦在夏天 2024-10-12 09:31:00

拉马克有点正确，但这就是我最终所做的，

SELECT *, distance('+lat+','+lon+', lat, lon) as minmiles
FROM all_restaurants 
WHERE restaurant_id || minmiles IN 
( 
    SELECT restaurant_id || MIN(miles) as fewmiles 
    FROM ( 
        SELECT restaurant_id, distance('+lat+','+lon+', lat, lon) as miles 
        FROM restaurant_master_combined 
        WHERE lat BETWEEN $lat AND $lat2 AND lon BETWEEN $lon1 AND $lon2 AND miles < $miles
    ) 
    GROUP BY restaurant_id 
)
ORDER BY ROUND(minmiles) ASC, company_name ASC

希望这对其他人有帮助

Lamak was somewhat right but this is what i ended up doing instead

SELECT *, distance('+lat+','+lon+', lat, lon) as minmiles
FROM all_restaurants 
WHERE restaurant_id || minmiles IN 
( 
    SELECT restaurant_id || MIN(miles) as fewmiles 
    FROM ( 
        SELECT restaurant_id, distance('+lat+','+lon+', lat, lon) as miles 
        FROM restaurant_master_combined 
        WHERE lat BETWEEN $lat AND $lat2 AND lon BETWEEN $lon1 AND $lon2 AND miles < $miles
    ) 
    GROUP BY restaurant_id 
)
ORDER BY ROUND(minmiles) ASC, company_name ASC

hope this helps somebody else

回复收藏 0 原文

~没有更多了~