mysql - 带连接二并按顺序排序的SQL

我有以下返回下表的查询：

(select trace_3733_3742.Sequance,trace_3733_3742.MainSequenceNum,trace_3733_3742.hopAddress addr from `trace_3733_3742` where MainSequenceNum = 5668799415 or MainSequenceNum = 5671689631);


+----------+-----------------+------------+
| Sequance | MainSequenceNum | addr       |
+----------+-----------------+------------+
|        1 |      5668799415 | 2229485073 |
|        3 |      5668799415 | 2229496574 |
|        4 |      5668799415 | 2258501244 |
|        5 |      5668799415 | 3286073269 |
|        6 |      5668799415 | 3241391462 |
|        7 |      5668799415 | 3241390941 |
|        8 |      5668799415 | 3241393449 |
|        9 |      5668799415 | 3241393534 |
|       10 |      5668799415 | 3561607085 |
|       11 |      5668799415 |   71666625 |

...
...
我还有一张桌子

(SELECT latitude,longitude FROM `GeoLiteCity_Oct2011` WHERE (start_ip_num <= hopaddress) ORDER BY start_ip_num DESC LIMIT 1);

hopaddress=上表中的hopaddress之一
例如：

(SELECT latitude,longitude FROM `GeoLiteCity_Oct2011` WHERE (start_ip_num <= 3561607199) ORDER BY start_ip_num DESC LIMIT 1);

会返回我想要的结果
桌子看起来像：

+--------------+------------+----------+-----------+
| start_ip_num | end_ip_num | latitude | longitude |
+--------------+------------+----------+-----------+
|            0 |          0 |     NULL |      NULL |
|     16777216 |   16777471 |      -27 |       133 |
|     16777472 |   16778239 |       35 |       105 |
|     16778240 |   16779263 |      -27 |       133 |
|     16779264 |   16781311 |       35 |       105 |
|     16781312 |   16785407 |       36 |       138 |

...
...
我想做的是返回第一个表的查询，每个表的纬度和经度为每一行-但我似乎做不到
我尝试了以下方法：

select * from (select trace_3733_3742.Sequance,trace_3733_3742.MainSequenceNum,trace_3733_3742.hopAddress addr from `trace_3733_3742` where MainSequenceNum = 5668799415 or MainSequenceNum = 5671689631) trace
join GeoLiteCity_Oct2011 loc
where (loc.start_ip_num <= addr) ORDER BY loc.start_ip_num DESC LIMIT 1

但它只返回一个结果，而不是所有表：(

最佳答案

我想试试这样的：

SELECT m.Sequance,m.MainSequenceNum,m.hopAddress,
   (SELECT g.latitude,g.longitude FROM `GeoLiteCity_Oct2011` AS g
    WHERE g.start_ip_num <= m.hopAddress
    ORDER BY g.start_ip_num DESC LIMIT 1)
 FROM `trace_3733_3742` AS m
 WHERE m.MainSequenceNum = 5668799415 or m.MainSequenceNum = 5671689631;

关于mysql - 带连接二并按顺序排序的SQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20030863/