MySql:查找表2.name WHERE表1.Lat = table2.表1.LNG = table2.lng; - MySql: Find table2.name WHERE table1.lat = table2.lat AND table1.lng = table2.lng;

MySql: Find table2.name WHERE table1.lat = table2.lat AND table1.lng = table2.lng;

过去几天我一直在绞尽脑汁，试图找到这个问题的答案。我有一份GPS数据路线表。和一个分数表。我需要的是一个MySql语句，对于一个给定的路线，将告诉我的点。两个端点的名称。我可以写4个单独的语句，但我相信有更好的方法。

表有相似的列{rowid, userid, latitude, longitude}，使事情变得有趣:

路线。Name = '2011-06-27';

和

点。Name = 'location_1';

得到第一分和最后一分很容易。(最后一个点LIMIT前加DESC)

SELECT `rowid`, `latitude`, `longitude` FROM `$db`.`Route` 
 WHERE `userid` LIKE '$userid' AND `name` = '$name' ORDER BY `rowid`  LIMIT 1;

我一直在阅读有关OUTER JOIN的内容，并认为它可以工作，这是我所得到的最接近的。

SELECT DISTINCT a.`rowid`, a.`latitude`, a.`longitude`, b.`name` 
 FROM `$db`.`Route` a
 RIGHT OUTER JOIN `$db`.`Point` b ON a.`latitude` = b.`latitude`
 AND a.`longitude` = b.`longitude` WHERE a.`userid` LIKE '$userid'
 AND a.`name` = '$name';

所需输出:

rowwid |纬度|经度| name
"16660","49.22551","-122.57416","location_5"
"16792","49.16722109","-122.98667883","location_1"

实际输出:

rowwid |纬度|经度| name
"16791","49.16721654","-122.98723093","location_1"
"16792","49.16722109","-122.98667883","location_1"

我将非常感谢任何帮助我解决这个问题的人。如果需要进一步的细节，请告诉我。

==== EDIT ====

SELECT `name`, `latitude`, `longitude` FROM `$db`.`Point`
 WHERE (`latitude`,`longitude`) IN(
 SELECT `rowid`, `latitude`, `longitude` FROM 
 ( SELECT `rowid`, `latitude`, `longitude` FROM `$db`.`Route` 
 WHERE `userid` LIKE '$userid' AND `name` = '$name' ORDER BY `rowid`  LIMIT 1) as t1
UNION
 ( SELECT `rowid`, `latitude`, `longitude` FROM `$db`.`Route` 
 WHERE `userid` LIKE '$userid' AND `name` = '$name' ORDER BY `rowid` DESC LIMIT 1) )

问题在于这会生成Error Code: 1235 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

因此，我需要更多地尝试使用LIMIT获取结果或检索结果，然后使用结果生成第二个查询。我稍后再打给你。再次感谢。

您可以使用UNION来组合两个sql。

SELECT `rowid`, `latitude`, `longitude` FROM 
( SELECT `rowid`, `latitude`, `longitude` FROM `$db`.`Route` 
 WHERE `userid` LIKE '$userid' AND `name` = '$name' ORDER BY `rowid`  LIMIT 1) AS t0
UNION
( SELECT `rowid`, `latitude`, `longitude` FROM `$db`.`Route` 
 WHERE `userid` LIKE '$userid' AND `name` = '$name' ORDER BY `rowid` DESC LIMIT 1) AS t1

我将语句分解并分离成一个语句以收集端点，然后循环结果以查询名称。我真的很想一次得到所有的结果，以减少处理大量用户的流量='

SELECT TRUNCATE(`latitude`,3) AS latitude, TRUNCATE(`longitude`,3) AS longitude 
 FROM `$db`.`$table1`
 WHERE `rowid` = (
  SELECT MIN(`rowid`) 
  FROM `$db`.`$table1`
  WHERE `userid` LIKE '$userid' AND `name` = '$name') 
UNION
 SELECT TRUNCATE(`latitude`,4) AS latitude, TRUNCATE(`longitude`,3) AS longitude
 FROM `$db`.`$table1`
 WHERE `rowid` = (
  SELECT MAX(`rowid`) 
  FROM `$db`.`$table1`
  WHERE `userid` LIKE '$userid' AND `name` = '$name')

用这条语句循环前一个查询的结果，以获得显示结束点的点。

SELECT name, latitude, longitude FROM `$db`.`$table2`
WHERE TRUNCATE(latitude,3) ='$lat' AND TRUNCATE(longitude,3) ='$lng' LIMIT 1;