mysql - 使用第一个 select 语句的结果到另一个 select 语句并将结果存储到另一个表中

Question

经过大量阅读后，我已经编写了这 3 个 sql 语句，并且我想自动化并组合这 3 个 sql 语句。

select longitude,latitude from roadData;

SELECT geo_coordinates_latitude, geo_coordinates_longitude,
 ( 3959 * acos( cos( radians('-37.964637158') ) * cos( radians( geo_coordinates_latitude ) ) * 
 cos( radians( geo_coordinates_longitude ) - radians('144.408476215') ) + sin( radians('-37.964637158') ) *
 sin( radians( geo_coordinates_latitude ) ) ) ) AS distance 
 FROM tweetmelbourne HAVING distance < .1 ORDER BY distance LIMIT 0 , 20;

Insert into roadTweets(longitude,latitude) values(144.408476215,-37.964637158);

**

What basically i want to do is pick all the coordinates from roadData one by one and then find all the point in tweetMelbourne within 20 miles of it and insert those point into another table.

**

describe tweetmelbourne;

describe roadData;

Answer 1

我对此并不完全确定，所以请告诉我这是否有效:

Insert into roadTweets(longitude,latitude) values(SELECT geo_coordinates_latitude, geo_coordinates_longitude,
 ( 3959 * acos( cos( radians(SELECT latitude FROM roadData) ) * cos( radians( geo_coordinates_latitude ) ) * 
 cos( radians( geo_coordinates_longitude ) - radians(SELECT longitude FROM roadData) ) + sin( radians(latitude) ) *
 sin( radians( geo_coordinates_latitude ) ) ) ) AS distance 
FROM tweetmelbourne HAVING distance < .1 ORDER BY distance LIMIT 0 , 20);