根据别名列名进行过滤
我正在使用 SqlServer 2005,并且有一个我命名的列。
查询类似于:
SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
FROM myTable
WHERE myAlias IS NOT NULL
但是,这给了我错误:
“列名‘myAlias’无效。”
有办法解决这个问题吗?过去,我在 WHERE 或 HAVING 部分中包含了列定义,但这些大多都很简单,IE COUNT(*) 或其他什么。我可以在此临时查询中包含整个列定义,但如果由于某种原因我需要在生产查询中执行此操作,我宁愿只将列定义包含一次,这样我就不必同时更新两者(并且忘记在某个时候做一件事)
I'm using SqlServer 2005 and I have a column that I named.
The query is something like:
SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
FROM myTable
WHERE myAlias IS NOT NULL
However, this gives me the error:
"Invalid column name 'myAlias'."
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)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
您不能在这样的 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:
使用 CTE 也是一种选择:
Using CTEs is also an option:
将相同的
CASE
语句放在WHERE
子句中:EDIT
另一种选择是嵌套查询:(
编辑:删除
HAVING 选项,因为这是不正确的(感谢@OMG Ponies))
Put the same
CASE
statement in theWHERE
clause:EDIT
Another option is to nest the query:
(Edit: removed
HAVING
option, as this was incorrect (thanks @OMG Ponies))把箱子放在哪里。 SQL Server 将足够聪明,只需对其进行一次评估,这样您就不必真正重复代码:
您可以将其包装在派生表中:
但是,我尽量避免使用没有限制性 WHERE 的派生表。您可以尝试一下,看看是否影响性能。
put the case in the where. SQL Server will be smart enough to just evaluate it one time so you aren't really duplicating the code:
you could wrap it in a derived table:
However, I try to avoid having derived tables without a restrictive WHERE. You can try it to see if it affects performance or not.
我最终创建了一个临时表来执行此操作。这里有一些伪代码可以给您一个想法。这适用于复杂的连接,我在这里仅展示一个简单的案例。
I ended up creating a temp table to do this. Here is some pseudo code to give you an idea. This worked with a complex join, I am just showing a simple case here.