在 where 子句 SQL 中引用计算列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9720790/
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
Referencing a calculated column in the where clause SQL
提问by tschock
This line of code is a snippet from my select statement.
这行代码是我的 select 语句中的一个片段。
frdFreedays - DateDiff(dd,conReceiptToStock,GetDate()) As FreeDaysRemaining
Below is a snippet from my where clause
下面是我的 where 子句的片段
and frdFreedays - DateDiff(dd,conReceiptToStock,GetDate()) <= @intFreeDays
The question I have is how can I reference the FreeDaysRemaining column and so I can compare it to @intFreeDays
我的问题是如何引用 FreeDaysRemaining 列，以便将其与 @ 进行比较intFreeDays
I am looking for something like this
我正在寻找这样的东西
Freedays <= @intFreeDays
采纳答案by Joey
In addition to Aaron's answer, you could use a common table expression:
除了 Aaron 的回答之外，您还可以使用通用表表达式：
;with cte_FreeDaysRemaining as
(
select
frdFreedays - DateDiff(dd,conReceiptToStock,GetDate()) As FreeDaysRemaining
--, more columns
from yourtable
)
select
FreeDaysRemaining
--, more columns
from cte_FreeDaysRemaining
where FreeDaysRemaining <= @intFreeDays
回答by Aaron Bertrand
You can't reference an alias anywhere except ORDER BY. One workaround (aside from the obvious possibility of repeating the expression) is to put it in a derived table:
除了ORDER BY. 一种解决方法（除了明显的重复表达式的可能性）是将它放在派生表中：
SELECT FreeDaysRemaining --, other columns
FROM
(
SELECT frdFreedays - DATEDIFF(DAY, conReceiptToStock, GETDATE()) AS FreeDaysRemaining
--, other columns
FROM ...
) AS x
WHERE FreeDaysRemaining <= @intFreeDays;
