计算列上的 T-SQL 列别名 - 列名无效 [英] T-SQL Column alias on computed column - Invalid column name

问题描述

我使用别名来引用计算列.这是我正在尝试编写的实际代码的片段，用于计算相似度并返回相似度得分为 3 或更高的匹配项.

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.

select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]
where similarity > 2
order by similarity desc

异常信息:

无效的列名相似性".

由于相似性不是一个真正的列，我将如何进行这项工作?

As similarity is not a real column, how would I make this work?

推荐答案

列别名和计算在查询的投影 (SELECT) 阶段执行，该阶段发生在选择 (WHERE 和 JOIN) 阶段.因此，无法在 WHERE 子句或 JOIN 条件中引用它们，因为它们尚不存在.您可以将带有 SELECT 子句的查询用作子查询，也可以在 WHERE 子句中复制计算:

Column aliases and computations are performed in the projection (SELECT) phase of the query, which occurs after the selection (WHERE and JOIN) phase. Because of this, they can't be referenced in the WHERE clause or in a JOIN condition because they do not yet exist. You can either use your query with the SELECT clause as a subquery or you can duplicate the computation in the WHERE clause:

select * 

from
(select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]) src

where similarity > 2
order by similarity desc

或

select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]
where difference([FirstName], 'mitch') > 2
order by similarity desc

这篇关于计算列上的 T-SQL 列别名 - 列名无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！