mysql - 连接两个并按限制排序的 SQL

Question

我有以下查询返回下表:

(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 = 上表中的跳地址之一

例如:

(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

但它只会返回 1 个结果，而不是所有表 :(

Answer 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;