我正在建立一个可以在附近位置找到商品的购物系统。基本上,每个位置(供应商)在名为“位置”的表中都有一个条目,其中包含名称,纬度,经度和其他列...然后有一个项目表,带有外键,用于标识该项目所属的位置至。
基本上,我想查找用户附近的所有项目:我可以使用以下查询进行操作:
但是我想做以下事情
-从位置随机选择项目
-将每个位置的项目数限制为几个(例如5个)
-将所有结果随机化,以便不按位置分组显示
到目前为止,这是我必须完成的困难查询,即时查询功能虽然有所改善,但仍然受困于此-任何帮助,如果非常感谢!谢谢!!
到目前为止,这是我的选择语句:
SELECT Location.idLocation
, Location.locationName
, Location.tagline
, Location.tags
, Location.shortAddress
, (3959 * acos(cos(radians('40.181')) * cos(radians(Location.latitude)) * cos(radians(Location.longitude) - radians('-74.0265')) + sin(radians('40.181')) * sin(radians(Location.latitude)))) AS distance
, Item.idItem
, Item.dateAdded
, Item.fidLocation
, Item.itemName
, Item.description
, Item.fullImageName
, Item.thumbnailImageName
FROM
Location
INNER JOIN Item
ON Location.idLocation = Item.fidLocation
HAVING distance < '1000'
最佳答案
好的。这里是基于假设的未经测试的版本。
SELECT
Location.idLocation,
Location.locationName,
Location.tagline,
Location.tags,
Location.shortAddress,
(3959 * acos(cos(radians('40.181')) * cos(radians(Location.latitude)) * cos(radians(Location.longitude) - radians('-74.0265')) + sin(radians('40.181')) * sin(radians(Location.latitude)))) AS distance,
Item.idItem,
Item.dateAdded,
Item.fidLocation,
Item.itemName,
Item.description,
Item.fullImageName,
Item.thumbnailImageName
FROM Location
INNER JOIN (select *
from Item
order by RAND()
limit 5) as Item
ON Location.idLocation = Item.fidLocation
ORDER BY RAND()
HAVING distance < '1000'