在不重复代码的情况下，最有效的方法是使用HAVING而不是WHERE

SELECT logcount, logUserID, maxlogtm
   , DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
HAVING daysdiff > 120

SELECT
   logcount, logUserID, maxlogtm,
   DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE ( DATEDIFF(day, maxlogtm, GETDATE() > 120)

通常不能在WHERE子句中引用字段别名。(可以把它看作包括别名在内的整个SELECT应用在WHERE子句之后。)

但是，正如在其他回答中提到的，您可以强制SQL将SELECT处理为在WHERE子句之前处理。这通常用圆括号来强制逻辑操作顺序或使用公共表表达式(CTE):

括号/子查询:

SELECT
   *
FROM
(
   SELECT
      logcount, logUserID, maxlogtm,
      DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
   FROM statslogsummary   
) as innerTable
WHERE daysdiff > 120

或者看看亚当的CTE版本的答案。

如果你想在WHERE子句中使用别名，你需要将它包装在一个子选择中，或CTE:

WITH LogDateDiff AS
(
   SELECT logcount, logUserID, maxlogtm
      , DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
   FROM statslogsummary
)
SELECT logCount, logUserId, maxlogtm, daysdiff
FROM LogDateDiff
WHERE daysdiff > 120

来到这里看起来类似，但有一个CASE WHEN，并像这样使用where结束:where (CASE WHEN COLUMN1=COLUMN2 THEN '1' ELSE '0' END) = 0也许你可以直接在where中使用DATEDIFF。 喜欢的东西:

SELECT logcount, logUserID, maxlogtm
FROM statslogsummary
WHERE (DATEDIFF(day, maxlogtm, GETDATE())) > 120

对我来说，在WHERE子句中使用ALIAS的最简单方法是创建子查询并从中进行选择。

例子:

WITH Q1 AS (
    SELECT LENGTH(name) AS name_length,
    id,
    name
    FROM any_table
)

SELECT id, name, name_length FROM Q1 WHERE name_length > 0

如果你不想在CTE中列出你所有的列，另一种方法是使用outer apply:

select
    s.logcount, s.logUserID, s.maxlogtm,
    a.daysdiff
from statslogsummary as s
    outer apply (select datediff(day, s.maxlogtm, getdate()) as daysdiff) as a
where a.daysdiff > 120