在 WHERE 子句中引用列别名 [英] Referring to a Column Alias in a WHERE Clause

问题描述

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

我明白

无效的列名 daysdiff".

"invalid column name daysdiff".

Maxlogtm 是一个日期时间字段.这是让我发疯的小事.

Maxlogtm is a datetime field. It's the little stuff that drives me crazy.

推荐答案

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

通常您不能在 WHERE 子句中引用字段别名.(可以将其视为包括别名在内的整个 SELECT 应用在 WHERE 子句之后.)

Normally you can't refer to field aliases in the WHERE clause. (Think of it as the entire SELECT including aliases, is applied after the WHERE clause.)

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

But, as mentioned in other answers, you can force SQL to treat SELECT to be handled before the WHERE clause. This is usually done with parenthesis to force logical order of operation or with a Common Table Expression (CTE):

括号/子选择:

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

或者查看 Adam 对 CTE 版本的回答.

Or see Adam's answer for a CTE version of the same.

这篇关于在 WHERE 子句中引用列别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！