mysql - SQL查询别名说明

Question

我在运行此查询时遇到问题。它应该存储为一个过程，这就是我正在做的事情。我无法弄清楚几个别名所指的是什么。

完整来源链接: http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

代码:

CREATE PROCEDURE geodist (IN userid int, IN dist int)

BEGIN

DECLARE mylon double;
DECLARE mylat double;
DECLARE lon1 float;
DECLARE lon2 float;
DECLARE lat1 float;
DECLARE lat2 float;

SELECT longitude, latitude into mylon, mylat from users WHERE id=userid limit 1;

SET lon1 = mylon-dist/abs(cos(radians(mylat))*69);
SET lon2 = mylon+dist/abs(cos(radians(mylat))*69);
SET lat1 = mylat-(dist/69);
SET lat2 = mylat+(dist/69);


SELECT destination.*, 3956 * 2 * ASIN(SQRT( POWER(SIN((orig.lat -dest.lat) * pi()/180 / 2), 2) +COS(orig.lat * pi()/180) * COS(dest.lat * pi()/180) *POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2) )) AS distance FROM users destination, users origin WHERE origin.id=userid
and destination.longitude between lon1 and lon2 and destination.latitude between lat1 and lat2 
having distance < dist ORDER BY distance limit 10;

END

The Issue I Am Having: In the actual select statement it almost immediately refers to orig.lat and dest.lat, also orig.lon and dest.lon. I can't find these references anywhere. What are these referring to?

当我尝试运行此查询时，出现错误:

"Unknown Column orig.lat"

Answer 1

您的表在用户目的地和用户来源分别被别名为目的地和来源。

您可能希望将 orig.lat -dest.lat 替换为 origin.lat -destination.lat。您也可以对其他引用文献执行相同的操作。