SQLite SQL查询的问题

Question

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

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

但我遇到了以下错误：

SQLSTATE[HY000]：一般性错误：在HAVING之前需要一个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;

没有错误，但是所有的记录都被返回了（即使它们具有“距离”>？）。我还尝试了以下操作：

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

输出结果相同，所有记录都被返回。我已经仔细检查过了——距离计算是正确的……我不知道这个查询有什么问题，请有经验的人帮帮我吧。

- Alix Axel

Answer 1

如果你没有指定GROUP BY子句，就不能指定HAVING子句。请使用以下语法：

  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;

- OMG Ponies

---

但我确实尝试过指定GROUP BY子句...有没有办法在不调用两次DISTANCE函数的情况下运行查询？这将使查询运行速度大大提高。 - Alix Axel

嗯...我也不是很喜欢嵌套查询，http://www.arubin.org/files/geo_search.pdf的第8页更加直观。MySQL手册(http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html)说：“如果HAVING子句没有被包含在聚合函数中，SQL标准不允许HAVING子句命名任何未在GROUP BY子句中找到的列。”这是SQLite特有的吗？很抱歉让你困扰了，还有其他解决方法吗？ - Alix Axel

@Alix：减少使用“DISTANCE”的次数没有其他选项。 “DISTANCE”不是聚合函数，因此比较应该放在“WHERE”子句中。使用“HAVING”的唯一原因是因为您实际上正在对记录进行分组-这对于一个国家表来说是不太可能的。 - OMG Ponies

Answer 2

一个更好（更快）的方法可能是在应用ORDER BY之前减少SELECT的集合。我使用这种方法：

SELECT * 
FROM Locations 
WHERE abs(Latitude - 51.123) < 0.12 
AND abs(Longitude - 0.123) < 0.34 
ORDER BY DISTANCE(Latitude, Longitude, 51.123, 0.123)

...其中 (51.123, 0.123) 是你相对于搜索的中心纬度/经度点，而 0.12 和 0.34 的值用于将搜索缩小到适当大小的纬度/经度方形区域（即在该点上地球球体上的 n 公里 x n 公里的正方形，其中大小取决于你的位置的平均地理分布情况）。我使用http://en.wikipedia.org/wiki/Longitude中的度长公式来计算这些值，以确定给定搜索点在地球球体上的位置。

- Dave Addey

---

谢谢。我正在做那个，只是没有发布以避免添加太多杂乱内容。=) - Alix Axel

Answer 3

这是语法错误，当您使用having子句时，必须使用'group by'

您的带有group by的查询正在获取具有("distance" >)的记录，因为，有数据库规则首先获取匹配记录的数据，然后在此基础上执行group by，最后通过having子句过滤记录。所以您永远不会获取到具有("distance" <)的数据

如果我错了，请纠正我

- chirag

---

谢谢，我不知道那个。有没有办法重写查询，使其仅返回具有“distance <=？”的记录，而无需两次调用“DISTANCE”函数？ - Alix Axel

Answer 4

进一步说，如果您不想两次调用DISTANCE函数，请在WHERE子句中引用别名，即：

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

- SimonDavies

---

看一下我问题中的最后一个代码片段... - Alix Axel