SQLite SQL查询出现问题 [英] Trouble with SQLite SQL Query

问题描述

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

I'm trying to run the following query in SQLite 3:

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

但是出现以下错误:

SQLSTATE [HY000]:常规错误:1 a 必须先使用GROUP BY子句 拥有

SQLSTATE[HY000]: General error: 1 a GROUP BY clause is required before HAVING

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

I don't understand why SQLite wants me to group results, but still I tried the following:

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" > ?的记录).我也尝试这样做:

No errors, but all records were returned (even those having "distance" > ?). I also tried doing:

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

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

Same output, all records were returned. I've double checked - the distance is being correctly calculated... I've no idea what's wrong with this query, can someone help me out?

推荐答案

如果不指定GROUP BY子句，则不能指定HAVING子句.使用:

You can't specify a HAVING clause without having specified a GROUP BY clause. Use:

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

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

If you don't want to call DISTANCE more than once, you can use a subquery:

  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;

这篇关于SQLite SQL查询出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！