如何创建为每个用户查找最近的存储的 JOIN 查询

Question

我有两个简单的表格：

User:
id - int
name - string
lat - decimal
long - decimal
Store:
id - int
name - string
lat - decimal
long - decimal

我想有一个查询，让所有用户都有最近的商店。我不在乎地球的圆度，因为每个用户都会在附近有一家商店。这就是为什么我选择使用毕达哥拉斯来寻找最近的距离：

SELECT 
   User.*, 
   Store.*, 
   Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
FROM User, Store

不幸的是，这给了我笛卡尔产品，所以我得到了所有用户与每个商店的距离。有没有办法只获得最近的商店？

谢谢

Answer 1

您可以

按用户分组并计算最小(距离(，然后链接回第一个查询以确定哪个商店与用户的距离。

这是您的查询，为清楚起见，删除了一些字段(并 User.Id 别名(

SELECT 
    User.id as userid, 
    Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
FROM User, Store

这将包装第一个查询，以计算每个用户到商店的最小距离

select userid, min(distance) from (
    SELECT 
        User.id as userid, 
        Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
    FROM User, Store
 ) as x 
group by userid

将其联接回原始查询以填充用户字段，并确定哪个商店与用户之间的(最小(距离

select z.*
from (
    select userid, min(distance) as distance from (
        SELECT 
            User.id as userid, 
            Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
        FROM User, Store
     ) as x 
group by userid
) as y inner join
(
    SELECT 
        User.Id as UserId, ... (populate the remaining user fields), 
        Store.*, 
        Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
    FROM User, Store
) as z
on y.userid = z.userid and y.distance = z.distance

Answer 2

您所需要的只是交叉连接表和一个 WHERE 子句，您可以在其中选择与每个用户保持最小距离的存储，如下所示：

select u.*, s.*
from user as u, store as s
where ((u.Lat - s.Lat)^2+(u.Long - s.Long)^2) = (
  select min((u.Lat - ss.Lat)^2+(u.Long - ss.Long)^2)
  from  store as ss
)

无需将Sqr()应用于计算。