如何摆脱 HAVING 子句中的非分组字段
在 ONLY_FULL_GROUP_BY 模式下执行时，执行以下查询时收到错误“HAVING 子句中使用了非分组字段“距离””。该查询统计某个纬度和距离 15 公里范围内的酒店数量。经度。有没有办法重写此查询，以便我在 ONLY_FULL_GROUP_BY 模式下不再收到错误？
SELECT count(id) as total, (foo * 100) AS 'distance'
FROM `hotels`
WHERE `lng` between 4.56 and 5.08 and `lat` between 52.22 and 52.65
HAVING `distance` < 15
When executing in ONLY_FULL_GROUP_BY mode, I get the error "non-grouping field 'distance' is used in HAVING clause" when executing the following query. The query counts the amount of hotels that are within 15 km distance of a certain latitude & longitude. Is there a way to rewrite this query so I don't get the error anymore in ONLY_FULL_GROUP_BY mode?
SELECT count(id) as total, (foo * 100) AS 'distance'
FROM `hotels`
WHERE `lng` between 4.56 and 5.08 and `lat` between 52.22 and 52.65
HAVING `distance` < 15
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
将 HAVING 更改为附加 WHERE 子句：
我不确定 MySQL 是否支持在 WHERE 子句中使用别名列名（在本例中为
distance）。如果不是，只需将其更改为：Change the HAVING to an additional WHERE clause:
I'm not sure if MySQL supports using the aliased column name in the WHERE clause (
distancein this case). If not, just change it to: