mysql空间距离的计算及距离排序

Question

我有三个表，包含以下字段：

1. org (org_id，org_name，location)
2. office (office_id，office_name，location，org_id)
3. 项目 (project_id，project_name，location)

我想找到与给定项目最接近的办公室

SELECT office.office_name, org.org_name, 
       st_distance_sphere(office.location, project.location)*0.001 as 
       distance 
FROM   office JOIN org JOIN project
WHERE  office.org_id=org.org_id AND 
       project.project_id=7 
ORDER BY distance asc;

上面的查询工作，给我与项目最近的办公室，但我只需要每个组织得到一个最近的办公室。

因此，我尝试了，以下使用半连接

SELECT office.office_name, org.org_name, 
       st_distance_sphere(office.location, project.location)*0.001 as  
       distance 
FROM  office JOIN org JOIN project  
INNER JOIN 
  ( SELECT org.org_id as orgid1, 
       min(st_distance_sphere(office.location, project.location)*0.0001) as 
       distance1 
   FROM office JOIN org JOIN project 
   WHERE 
      office.org_id=org.org_id and project.project_id=7
   GROUP BY org.org_id
   ) AS t 
     ON t.orgid1=org.org_id and t.distance1=min(st_distance_sphere(office.location,project.location)*0.001) 
 WHERE  office.org_id=org.org_id AND project.project_id=7 ;

使用它，我得到以下错误，原因似乎是min(st_distance_sphere)函数。删除该操作将消除错误，但我无法获得所需的结果。

错误1111 (HY000)：组函数使用无效

我做错什么了？任何想法都值得高度赞赏。谢谢

Answer 1

我只需要每个组织得到一个最近的办公室

SELECT 
  A.office_name, A.org_name, A.distance
FROM
  (SELECT 
    office.office_name,
    org.org_name, 
    st_distance_sphere(office.location, project.location)*0.001 as distance 
  FROM  office JOIN org JOIN project
  WHERE  office.org_id=org.org_id AND 
       project.project_id=7) A 
  JOIN 
  (SELECT 
     org.org_name, 
     MIN(st_distance_sphere(office.location, project.location)*0.001) as distance 
  FROM   office JOIN org JOIN project
  WHERE  office.org_id=org.org_id AND 
       project.project_id=7
  GROUP BY org.org_name) B
  ON A.org_name=B.org_name AND A.distance=B.distance
ORDER BY A.distance ASC;