sql - MySQL 语法按餐厅分组，菜单更便宜

Question

这是我对 MySQL (MySQL: 5.0.51a) 的 SQL 请求。我想要一份包含他更便宜菜单的餐厅列表:

select r.id, rm.id, rm.price, moyenne as note, 
get_distance_metres('47.2412254', '6.0255656', map_lat, map_lon) AS distance 

from restaurant_restaurant r 
LEFT JOIN restaurant_menu rm ON r.id = rm.restaurant_id 

where r.isVisible = 1 

group by r.id 
having distance < 2000 
order by distance ASC 
limit 0, 10

如果我不使用 group by，我有一个包含我所有菜单和餐厅的列表，但是当我使用它时，看起来他随机为我的餐厅选择了一个菜单。

感谢您的帮助。

Answer 1

类似的东西是行不通的:

SELECT r.id as restaurant_id, rm.id as menu_id, rm.price as price, r.moyenne AS note, 
get_distance_metres('47.2412254', '6.0255656', map_lat, map_lon) AS distance 

FROM restaurant_restaurant r 
LEFT JOIN restaurant_menu rm ON r.id = rm.restaurant_id 

WHERE r.isVisible = 1 

GROUP BY r.id 
HAVING MIN(price) AND distance < 120000
ORDER BY price ASC, distance ASC 
LIMIT 0, 10