WHERE 语句中的 SQL Server AS 语句别名列

SQL Server AS statement aliased column within WHERE statement

提问by GobiasKoffi

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:

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

SELECT lat AS latitude 
FROM poi_table 
WHERE latitude < 500

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?

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

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

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

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;

回答by OMG Ponies

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.

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

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.

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

Update

更新

---

---

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;

...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:

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

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;

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

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

回答by tgolisch

SQL Server is tuned to apply the filters before it applies aliases (because that usually produces faster results). You could do a nested select statement. Example:

SQL Server 已调整为在应用别名之前应用过滤器（因为这通常会产生更快的结果）。您可以执行嵌套的 select 语句。例子：

SELECT Latitude FROM 
(
    SELECT Lat AS Latitude FROM poi_table
) A
WHERE Latitude < 500

I realize this may not be what you are looking for, because it makes your queries much more wordy. A more succinct approach would be to make a view that wraps your underlying table:

我意识到这可能不是您要查找的内容，因为它使您的查询更加冗长。更简洁的方法是创建一个包含基础表的视图：

CREATE VIEW vPoi_Table AS 
SELECT Lat AS Latitude FROM poi_table

Then you could say:

那你可以说：

SELECT Latitude FROM vPoi_Table WHERE Latitude < 500

回答by buckbova

I am not sure why you cannot use "lat" but, if you must you can rename the columns in a derived table.

我不确定为什么不能使用“lat”，但是，如果必须，您可以重命名派生表中的列。

select latitude from (SELECT lat AS latitude FROM poi_table) p where latitude < 500

回答by Baaju

This would work on your edited question !

这将适用于您编辑的问题！

SELECT * FROM (SELECT <Column_List>,  
( 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) TMP
WHERE distance < 500; 

回答by nightlyop

Logical Processing Order of the SELECT statement

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

SELECT 语句的逻辑处理顺序

以下步骤显示了 SELECT 语句的逻辑处理顺序或绑定顺序。此顺序决定了在一个步骤中定义的对象何时可用于后续步骤中的子句。例如，如果查询处理器可以绑定（访问）在 FROM 子句中定义的表或视图，则这些对象及其列可用于所有后续步骤。相反，由于 SELECT 子句是第 8 步，因此该子句中定义的任何列别名或派生列都不能被前面的子句引用。但是，它们可以被后续子句引用，例如 ORDER BY 子句。请注意，语句的实际物理执行由查询处理器确定，并且顺序可能与此列表不同。

1. 从
2. 在
3. 加入
4. 在哪里
5. 通过...分组
6. WITH CUBE 或 WITH ROLLUP
7. 有
8. 选择
9. 清楚的
10. 订购者
11. 最佳

Source: http://msdn.microsoft.com/en-us/library/ms189499%28v=sql.110%29.aspx

来源：http: //msdn.microsoft.com/en-us/library/ms189499%28v=sql.110%29.aspx

回答by Lukasz Szozda

Both accepted answerand Logical Processing Orderexplain why you could not do what you proposed.

双方接受的答案和逻辑处理顺序解释为什么你不能做你什么建议。

Possible solution:

可能的解决方案：

• use derived table (cte/subquery)
• use expression in WHERE
• create view/computed column

• 使用派生表（cte/子查询）
• 使用表达式 WHERE
• 创建视图/计算列

---

---

From SQL Server 2008you could use APPLYoperator combined with Table valued Constructor:

从SQL Server 2008你可以使用APPLY运算符结合Table valued Constructor：

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

LiveDemo

LiveDemo