计算列上的 T-SQL 列别名 - 列名无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6591183/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL Column alias on computed column - Invalid column name
提问by Marcus K
I'm using an alias to refer to a computed column. Here is a snippet from the actual code I'm trying to make work, to compute similarity and return matches where the similarity score is 3 or higher.
我正在使用别名来引用计算列。这是我正在尝试制作的实际代码的片段，用于计算相似度并返回相似度分数为 3 或更高的匹配项。
select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]
where similarity > 2
order by similarity desc
Exception Message:
异常消息：
Invalid column name 'similarity'.
无效的列名“相似性”。
As similarity is not a real column, how would I make this work?
由于相似性不是真正的专栏，我将如何进行这项工作？
回答by Adam Robinson
Column aliases and computations are performed in the projection (SELECT) phase of the query, which occurs after the selection (WHEREand JOIN) phase. Because of this, they can't be referenced in the WHEREclause or in a JOINcondition because they do not yet exist. You can either use your query with the SELECTclause as a subquery or you can duplicate the computation in the WHEREclause:
列别名和计算SELECT在查询的投影 ( ) 阶段执行，该阶段发生在选择 (WHERE和JOIN) 阶段之后。因此，无法在WHERE子句或JOIN条件中引用它们，因为它们尚不存在。您可以将带有SELECT子句的查询用作子查询，也可以复制WHERE子句中的计算：
select *
from
(select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]) src
where similarity > 2
order by similarity desc
or
或者
select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]
where difference([FirstName], 'mitch') > 2
order by similarity desc
回答by ktutnik
All answers can solve your problem but for complicated situation you just can't duplicate your query.
所有答案都可以解决您的问题，但对于复杂的情况，您无法重复查询。
The correct way is by using CROSS and APPLY
正确的方法是使用CROSS 和 APPLY
select [FirstName], similarity
from [Dev].[dbo].[Name]
cross apply
(
select similarity =
difference([FirstName], 'mitch')
)computed_column
where similarity > 2
order by similarity desc
whit CROSS and APPLY you can use your computed column everywhere on the query
通过 CROSS 和 APPLY，您可以在查询的任何地方使用计算列
回答by Chandu
Try:
尝试：
SELECT *
FROM (
SELECT [FirstName], difference([FirstName], 'mitch') as similarity
FROM [Dev].[dbo].[Name]
) a
WHERE similarity > 2
ORDER BY similarity desc
回答by Bohemian
select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]
where difference([FirstName], 'mitch') > 2
order by 2 desc
You can't reference column aliases in where clause
您不能在 where 子句中引用列别名
