根据别名列名称进行过滤 [英] Filter based on an aliased column name

问题描述

我正在使用SqlServer 2005，并且有一个我命名的列。

I'm using SqlServer 2005 and I have a column that I named.

查询类似于：

SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
FROM myTable
WHERE myAlias IS NOT NULL

给我错误：

无效的列名'myAlias'。

"Invalid column name 'myAlias'."

有没有办法解决这个问题？过去，我在WHERE或HAVING部分中都包含了列定义，但这些定义大多是简单的IE COUNT（*）或其他内容。我可以在此临时查询中包括整个列定义，但是如果出于某种原因我需要在生产查询中执行此操作，则我希望只对列定义进行一次，这样就不必更新两者（并且忘记在某个时候做一个）

Is there a way to get around this? In the past I've included the column definition in either the WHERE or the HAVING section, but those were mostly simple, IE COUNT(*) or whatever. I can include the whole column definition in this ad-hoc query, but if for some reason I needed to do this in a production query I'd prefer to have the column definition only once so I don't have to update both (and forget to do one at some point)

推荐答案

您不能在where子句中引用别名...您要么必须在WHERE中复制CASE，或者您可以使用像这样的子查询：

You can't reference aliases in a where clause like that... you either have to duplicate the CASE in the WHERE, or you can use a subquery like this:

SELECT id, myAlias
FROM
(
    SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
    FROM myTable
) data
WHERE myAlias IS NOT NULL

这篇关于根据别名列名称进行过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！