mysql - SQL查询说明

Question

我在使用此 SQL 查询时遇到了一些问题。这实际上来自处理空间计算的过程。该过程查找特定半径内的位置。真的，我很难理解发生了什么。如果有人可以帮助解释几件事，我只需要为我的目的重写它。我将永远感激任何试图提供帮助的人。提前致谢!

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

完整过程 - 我只需要一些关于实际 SELECT 语句的解释帮助 - 在这里只是为了一些澄清，以便阅读的人可以更好地理解

CREATE PROCEDURE geodist (IN userid int, IN dist int)BEGINdeclare mylon double; declare mylat double;declare lon1 float; declare lon2 float;declare lat1 float; declare lat2 float;
-- get the original lon and lat for the userid 
select longitude, latitude into mylon, mylat from users5where id=userid limit 1;
-- calculate lon and lat for the rectangle:
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);
-- run the query:
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) )) asdistance FROM users destination, users originWHERE 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 $$

完整查询我需要澄清的部分:

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 locations.longitude between lon1 and lon2 and locations.latitude between lat1 and lat2 
having distance < dist ORDER BY Distance;

第一部分:

SELECT destination.*

Question: What is with the period and * after the word destination? Sure we are selecting destination but is this concatenating? Like prefixing the word destination to every column in the table?

下一部分:

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

Question: Is this just performing the calculation and storing it as the keyword destination? The keyword "as" is throwing me off here. Is it searching the database for a column destination?

第三部分:

FROM users destination, users origin WHERE origin.id=userid

Question: a little confused on where the query is headed. users destination? users origin? What is this grabbing?

最后一部分:

having distance < dist ORDER BY Distance;

Question: Confused about the "having" keyword and where having distance comes into play. Is it trying to grab distance from the table because this is something that is obviously calculated on the fly.

Answer 1

第一部分

* 选择所有列。您可以使用点表示法为特定表指定所有列，就像您用来为表指定单个列一样。例如:

SELECT t1.* FROM t1 NATURAL JOIN t2

即使在 JOIN 中使用了 t2，也只会从 t1 中选择 SELECT 列。

下一部分

这只是对某些列进行数学计算，并将结果别名为 distance(不是“目的地”)。您可以在结果集中将结果引用为 distance。 AS 关键字设置列别名，但它是可选的。

第三部分

destination 和 origin 是别名。他们放弃了可选的 AS 关键字。写成是一样的:

FROM users AS destination, users AS origin

users 表在查询期间被重命名，因此它可以被别名引用，同时也避免了查询中两个相同表名的冲突，这将是无效的.

最后一部分

distance 是上述数学计算的别名。 HAVING 类似于 WHERE，但它必须用于聚合(例如 GROUP BY)。我可能是错的，但我认为在此查询中没有必要。