MySQL查询从不同的表中获取数据并将其用于距离计算

I have two MySQL tables:

我有两个MySQL表:

CREATE TABLE IF NOT EXISTS `wp_zipcode` (
  `zip` int(5) NOT NULL AUTO_INCREMENT,
  `lat` float(10,6) NOT NULL,
  `lng` float(10,6) NOT NULL,
  PRIMARY KEY (`zip`)
)

and

CREATE TABLE IF NOT EXISTS `wp_events` (
  `EventID` int(11) NOT NULL,
  `Event` varchar(256) NOT NULL,
  `Zip` int(11) NOT NULL,

Unfortunately I cannot alter the wp_events(it gets overwritten with new data al the time) and I am doing a "distance" search for the nearest events to the user. I am using Google's Example found here

不幸的是,我不能改变wp_events(它随着时间的推移被新数据覆盖)而且我正在为用户进行最近事件的“距离”搜索。我在这里使用谷歌的例子

( 3959 * acos( cos( radians(34.070358) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-118.349258) ) + sin( radians(34.070358) ) * sin( radians( lat ) ) ) ) AS distance FROM wp_events HAVING distance < 25 ORDER BY distance LIMIT 0 , 20

(3959 * acos(cos(弧度(34.070358))* cos(弧度(纬度))* cos(弧度(lng) - 弧度(-118.349258))+ sin(弧度(34.070358))* sin(弧度(纬度)) ))距离距离wp_events距离<25 ORDER BY距离LIMIT 0,20

My issue is like this: For the algorithim to work, I need to have the lng and lat of a zipcode, but since Im searching in wp_events for the events, is there a way to get the event's zipcode from wp_events and somehow(this is where my question comes in) get the corresponding lat and lng for that specific zipcode from wp_zipcodes and return to the SQL statment to get the appropriate events near the desired zipcode.

我的问题是这样的:为了使算法工作,我需要有一个邮政编码的lng和lat,但是因为我在wp_events中搜索事件,是否有办法从wp_events中获取事件的zipcode(不知何故)在我的问题进来的地方)从wp_zipcodes获取该特定邮政编码的相应lat和lng并返回到SQL语句以获得所需邮政编码附近的相应事件。

thanks in advance

提前致谢

2 个解决方案

#1

SELECT wp_events.*, 
       wp_zipcode.lat, 
       wp_zipcode.lng,
       ( 3959 * acos( cos( radians(34.070358) ) * cos( radians( wp_zipcode.lat ) ) * cos( radians( wp_zipcode.lng ) - radians(-118.349258) ) + sin( radians(34.070358) ) * sin( radians( wp_zipcode.lat ) ) ) ) AS distance
  FROM wp_events 
  LEFT JOIN wp_zipcode 
    ON wp_zipcode.zip = wp_events.Zip
HAVING distance < 25 
 ORDER BY distance 
 LIMIT 0 , 20

#2

You can use JOIN, like this:

您可以使用JOIN,如下所示:

SELECT wp_events.EventId, wp_events.Event, wp_events.Zip, wp_zipcode.lat, wp_zipcode.lng
FROM wp_events
JOIN wp_zipcode ON wp_events.Zip = wp_zipcode.zip