SQLite SQL 查询出现问题[重复]

我正在尝试在 SQLite 3 中运行以下查询：

SELECT *,
  DISTANCE(latitude, longitude, ?, ?) AS "distance"
FROM "country"
WHERE "id" NOT LIKE ?
HAVING "distance" <= ?
ORDER BY "distance" ASC;

但我收到以下错误：

SQLSTATE[HY000]：一般错误：1 之前需要 GROUP BY 子句 拥有

我不明白为什么 SQLite 要我对结果进行分组，但我仍然尝试了以下操作：

SELECT *,
  DISTANCE(latitude, longitude, ?, ?) AS "distance"
FROM "country"
WHERE "id" NOT LIKE ?
GROUP BY "id"
HAVING "distance" <= ?
ORDER BY "distance" ASC;

我也尝试过这个：

SELECT *,
  DISTANCE(latitude, longitude, ?, ?) AS "distance"
FROM "country"
WHERE "id" NOT LIKE ?
GROUP BY "distance"
HAVING "distance" <= ?
ORDER BY "distance" ASC;

没有错误，但所有记录都已返回（即使是那些有"distance" > ?）。我也尝试这样做：

SELECT *,
  DISTANCE(latitude, longitude, ?, ?) AS "distance"
FROM "country"
WHERE "id" NOT LIKE ?
  AND "distance" <= ?
ORDER BY "distance" ASC;

相同的输出，返回所有记录。我已经仔细检查过 - 距离计算正确...我不知道这个查询出了什么问题，有人可以帮助我吗？

你不能指定一个HAVING条款没有指定GROUP BY条款。使用：

  SELECT *, 
         DISTANCE(latitude, longitude, ?, ?) AS dist
    FROM COUNTRY c
   WHERE c.id NOT LIKE ?
     AND DISTANCE(c.latitude, c.longitude, ?, ?) <= ?
ORDER BY dist;

如果您不想多次调用 DISTANCE，可以使用子查询：

  SELECT x.*
    FROM (SELECT c.*, 
                 DISTANCE(latitude, longitude, ?, ?) AS dist
            FROM COUNTRY c
           WHERE c.id NOT LIKE ?) x
   WHERE x.dist <= ? 
ORDER BY dist;