mysql where 多个字段

Question

我想根据城市和国家提取唯一的minlat、minlng，然后想找到所有具有这对的id，我的意思是这样的

select id from spots 
where (minlat,minlng) in 
    (select s.minlat, s.minlng from spots s, spot_cards sc 
    where sc.spot_id=s.id and sc.country="italy" 
    and 
        (sc.city="perugia" or sc.locality="perugia" or sc.sublocality="perugia")
    );

点表的结构是:

     +----+-----------+-----------+
     | id | minlat    | minlng    |
     +----+-----------+-----------+

我创建的spot_cards表结构为

    +---------+-------------+-------------+---------+--------+
    | spot_id | sublocality | locality    | country | city   |
    +---------+-------------+-------------+---------+--------+

通过执行以下查询

insert into spot_cards(spot_id) 
select id from spots 
group by minlat,minlng 
order by id

感谢任何帮助。

Answer 1

我认为你需要这样的东西:

连接点表以获取所需的所有记录，然后再次连接点表以获取坐标。执行 DISTINCT 来消除任何可能的重复项。

SELECT DISTINCT s2.id from spots s
INNER JOIN spot_cards sc
ON sc.spot_id=s.id
INNER JOIN spots s2
ON s.minlat = s2.minlat AND s.minlng = s2.minlng
WHERE  sc.country="italy" 
       AND (sc.city="perugia" or sc.locality="perugia" or sc.sublocality="perugia")