mysql - mysql 检索指定距离内的记录

Question

运行此查询时出现以下错误:

SELECT count(*) AS COUNT,
       ((ACOS(SIN(
        (SELECT latitude
         FROM cities
         WHERE name = 'london' & country = 'gb')*PI()/180) * 
         SIN((cities.latitude)*PI()/180)+COS(                                                                                                      
 (SELECT latitude
  FROM cities
  WHERE name = 'london' && country = 'gb')*PI()/180)*COS((cities.latitude)*PI()/180)*COS((
(SELECT longitude
 FROM cities
 WHERE name = 'london' && country = 'gb')-
(cities.longitude))*PI()/180))*180/PI())*60*1.1515) AS distance
FROM tbl_products
LEFT JOIN members ON tbl_products.seller = members.id
LEFT JOIN business ON tbl_products.seller = business.bid
INNER JOIN cars ON tbl_products.prod_id = cars.prod_id
LEFT JOIN car ON cars.car = car.id
LEFT JOIN cities ON members.location = cities.id
LEFT JOIN regions ON cities.region = regions.code && cities.country = regions.country
WHERE tbl_products.title LIKE '%|mazda%' ESCAPE '|'
OR tbl_products.description LIKE '%|mazda%' ESCAPE '|' && regions.country = 'gb' HAVING distance <= '30';

子查询返回多于 1 行。

我只是想知道如何检索给定距离内的记录。

Answer 1

该错误准确地表明了其内容。您正在执行子查询，但它返回多行，而上下文中只需要一行

SELECT count*(*) AS count, ACOS(SIN(...subselect here ...))

很难判断您正在执行的哪一个子选择导致了这种情况，但您必须找到哪一个，并让它只发回一行。由于您在进一步的函数调用中使用结果，因此您还必须确保只返回一个字段，例如

SELECT sin(select 1,2 from dual) from dual

会导致相关错误。