WHERE 语句中的 SQL Server AS 语句别名列 [英] SQL Server AS statement aliased column within WHERE statement

问题描述

我想执行一个查询，在该查询中我使用AS"语句重命名其中一个列，并在WHERE"语句中重用该别名列名.下面是一个例子:

I want to execute a query in which I rename one of the columns using the 'AS' statement and reuse that aliased column name within the 'WHERE' statement. Below is an example:

SELECT lat AS latitude 
FROM poi_table 
WHERE latitude < 500

这里的问题是 SQL Server 不喜欢这个查询，因为 WHERE 子句和 WHERE 子句中引用了 AS 语句名称.任何人都可以解释为什么会发生这种情况以及我可以做些什么来纠正我的情况?

The problem here is that SQL Server does not like this query because of the WHERE clause and the AS statement name being referenced in the WHERE clause. Can anyone explain why this is happening and what I can do to remedy my situation?

假设我在查询的 SELECT 部分有一个别名为别名的公式，我该如何解决?

Suppose I were to have a formula that I have aliased in the SELECT portion of the query, how do I tackle that?

SELECT *, 
( 6371*1000 * acos( cos( radians(42.3936868308) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-72.5277256966) ) + sin( radians(42.3936868308) ) * sin( radians( lat ) ) ) ) 
AS distance
FROM poi_table 
WHERE distance < 500;

推荐答案

SQL 通常不允许您在 WHERE、GROUP BY 或 HAVING 子句中引用列别名.MySQL 确实支持在 GROUP BY 和 HAVING 中引用列别名，但我强调，将此类查询移植到其他数据库时会出现问题.

SQL doesn't typically allow you to reference column aliases in WHERE, GROUP BY or HAVING clauses. MySQL does support referencing column aliases in the GROUP BY and HAVING, but I stress that it will cause problems when porting such queries to other databases.

如有疑问，请使用实际的列名:

When in doubt, use the actual column name:

SELECT t.lat AS latitude 
  FROM poi_table t
 WHERE t.lat < 500

我添加了一个表别名，以便更容易查看实际列与别名的区别.

I added a table alias to make it easier to see what is an actual column vs alias.

一个计算列，就像你在这里看到的那样:

A computed column, like the one you see here:

SELECT *, 
       ( 6371*1000 * acos( cos( radians(42.3936868308) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-72.5277256966) ) + sin( radians(42.3936868308) ) * sin( radians( lat ) ) ) ) AS distance
  FROM poi_table 
 WHERE distance < 500;

...不会改变您不能在 WHERE 子句中引用列别名.要使该查询起作用，您必须使用:

...doesn't change that you can not reference a column alias in the WHERE clause. For that query to work, you'd have to use:

SELECT *, 
       ( 6371*1000 * acos( cos( radians(42.3936868308) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-72.5277256966) ) + sin( radians(42.3936868308) ) * sin( radians( lat ) ) ) ) AS distance
  FROM poi_table
 WHERE ( 6371*1000 * acos( cos( radians(42.3936868308) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-72.5277256966) ) + sin( radians(42.3936868308) ) * sin( radians( lat ) ) ) ) < 500;

请注意，在列上使用函数(即:RADIANS(lat))将使索引无用，如果该列上存在索引.

Be aware that using a function on a column (IE: RADIANS(lat)) will render an index useless, if one exists on the column.

这篇关于WHERE 语句中的 SQL Server AS 语句别名列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！