MySQL COUNT(*）返回空结果集

我目前正在MySQL中的表上执行大圆距离计算。该表如下所示：

+----------+---------+---------+
| venue_id | lat     | lng     |
+----------+---------+---------+
| 1        | 1.23456 | 2.34567 |
+----------+---------+---------+
| 2        | 1.23456 | 2.34567 |
+----------+---------+---------+
| 3        | 1.23456 | 2.34567 |
+----------+---------+---------+

但是，当我尝试创建返回结果的COUNT()时，MySQL返回一个空结果集。

例如，以下查询不生成任何结果：

SELECT COUNT(*) AS total, 
       ( 3959 * Acos(Cos(Radians('52.97682200')) * Cos(Radians(lat)) * Cos( 
                                   Radians(lng) - Radians(-0.02210000)) + 
                              Sin(Radians(52.97682200)) * Sin(Radians(lat))) ) 
       AS distance 
FROM   wv_venue_locations 
HAVING distance < 5
ORDER  BY distance

但是，在没有COUNT(*) AS total字段的情况下运行相同的查询会导致预期的 6 个结果。

谁能建议为什么会发生这种情况，以及我该如何解决它？

作为上述内容的后脚本，以下内容工作正常：

SELECT Count(*) 
       AS 
       total, 
       ( 3959 * Acos(Cos(Radians(53.18335000)) * Cos(Radians(lat)) * Cos( 
                                   Radians(lng) - Radians(-0.29600000)) + 
                              Sin(Radians(53.18335000)) * Sin(Radians(lat))) ) 
       AS 
       distance 
FROM   wv_venue_locations 
WHERE  lat >= 52.64017900
       AND lat <= 53.72650900
       AND lng >= -0.94998000
       AND lng <= 0.35798000

以上正确输出：

+-------+-------------------+
| total | distance          |
+-------+-------------------+
| 224   | 27.93840157954865 |
+-------+-------------------+

要计算行数，您可以执行此操作

SELECT SQL_CALC_FOUND_ROWS 
       ( 3959 * Acos(Cos(Radians('52.97682200')) * Cos(Radians(lat)) * Cos( 
                                   Radians(lng) - Radians(-0.02210000)) + 
                              Sin(Radians(52.97682200)) * Sin(Radians(lat))) ) 
       AS distance 
FROM   wv_venue_locations 
HAVING distance < 5
ORDER  BY distance

选择所需的行后，可以使用以下单个查询获取计数：

SELECT FOUND_ROWS();

对此进行了测试，它似乎返回了您想要的内容。如果这不正确，请告诉我

SELECT SUM(1) as total,
       ( 3959 
        * Acos(  Cos(Radians('52.97682200'))
               * Cos(Radians(lat)) 
               * Cos(Radians(lng) - Radians(-0.02210000)) 
               + Sin(Radians(52.97682200)) 
               * Sin(Radians(lat))
              ) 
       ) AS distance 
FROM   wv_venue_locations 
GROUP BY distance
HAVING distance < 5
ORDER  BY distance

小提琴演示

主要问题是没有分组依据和 AGGREGATE 函数，它会将所有行分组为一行......你需要告诉它将它们分开。所以我说的是距离，因为你得到了一个经度和长度的距离列表。

编辑：

如果您希望返回一行，显示距离为 5 的总位置和总距离，您可以执行以下操作。

SELECT SUM(1) as total, SUM(distance) as total_distance, MAX(distance) as furthest_location, MIN(distance) as closest_location
FROM
(   SELECT
        ( 3959 
            * Acos(  Cos(Radians('52.97682200'))
                   * Cos(Radians(lat)) 
                   * Cos(Radians(lng) - Radians(-0.02210000)) 
                   + Sin(Radians(52.97682200)) 
                   * Sin(Radians(lat))
                  ) 
           ) AS distance 
    FROM   wv_venue_locations 
    HAVING distance < 5
    ORDER  BY distance
) t

第二个演示

我相信

我弄清楚为什么使用 HAVING 不会返回任何结果，因为HAVING会在分组后过滤行。由于您正在对整个表进行分组，因此将只返回一行。然后，它将按HAVING过滤。

这是更清晰的图片，

SELECT COUNT(*) AS total, (....) AS distance
FROM wv_venue_locations

此查询将返回一行。假设该行是

total, distance
100, 6.432

然后，应用HAVING distance < 5.没有符合此条件的行

我不知道这是否是OP想要的

SELECT COUNT(*), MIN(distance)
FROM (
  SELECT 
       ( 3959 * Acos(Cos(Radians('52.97682200')) * Cos(Radians(lat)) * Cos( 
                                   Radians(lng) - Radians(-0.02210000)) + 
                              Sin(Radians(52.97682200)) * Sin(Radians(lat))) ) 
       AS distance 
   FROM   wv_venue_locations
) AS t
WHERE distance < 5

您的distance < 5应该处于WHERE状态，而不是处于HAVING状态。如果您只想显示COUNT()输出满足特定条件的结果，则将使用HAVING。

MySQL COUNT(*） 返回空结果集

MySQL COUNT(*) returns empty result set

编辑：