MySQL查询分组和统计结果

Question

这是我当前的查询 - 它显示城市和距离值:

SELECT city, ( 6371 * acos( cos( radians(51.228482) ) * cos( radians( geo_breit
 ) ) * cos( radians( geo_lang ) - radians(6.772457) ) + sin( radians(51.228482) ) * sin( radians( geo_breit
 ) ) ) )
   AS distance
   FROM partner HAVING distance < 50 
   ORDER BY city

输出:

我完全迷失在对数据进行分组时，所以它会返回以下内容

city                    total         max_distance
-------------------------------------------------------------
Bedburg                 2             29.445788818447983
Bergheim                2             32.83341311371813
Bergisch Gladbach       4             39.62206615477636
Bochum                  1             43.15766028349356
...

编辑:我添加了实际查询，问题是使用HAVING关键字时分组/最大值不起作用..有什么想法吗？

Answer 1

要么使用像这样的嵌套查询:

SELECT cities.city,
       count(*) as total,
       max(cities.distance) as max_distance
FROM (SELECT city,
            ( 6371 * acos( cos( radians(51.228482) ) * cos( radians( geo_breit ) ) * cos( radians( geo_lang ) - radians(6.772457) ) + sin( radians(51.228482) ) * sin( radians( geo_breit) ) ) ) AS distance
       FROM partner HAVING distance < 50 
       ORDER BY city) as cities
  GROUP BY cities.city

或者您可以尝试使用 where 子句而不是having

    SELECT city, count(*),
            max( 6371 * acos( cos( radians(51.228482) ) * cos( radians( geo_breit ) ) * cos( radians( geo_lang ) - radians(6.772457) ) + sin( radians(51.228482) ) * sin( radians( geo_breit) ) ) ) AS max_distance
       FROM partner 
       WHERE (6371 * acos( cos( radians(51.228482) ) * cos( radians( geo_breit ) ) * cos( radians( geo_lang ) - radians(6.772457) ) + sin( radians(51.228482) ) * sin( radians( geo_breit) ) ) < 50 
       ORDER BY city
       GROUP BY city