SQL 在 WHERE 子句中引用列别名

Referring to a Column Alias in a WHERE Clause

提问by user990016

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

I get

我得到

"invalid column name daysdiff".

“无效的列名 daysdiff”。

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

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

回答by Jamie F

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

Normally you can't refer to field aliases in the WHEREclause. (Think of it as the entire SELECTincluding aliases, is applied after the WHEREclause.)

通常您不能在WHERE子句中引用字段别名。（将其视为整个SELECT包括别名，在WHERE子句之后应用。）

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

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

Parenthesis/Subselect:

括号/子选择：

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

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

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

回答by Adam Wenger

If you want to use the alias in your WHEREclause, you need to wrap it in a sub select, or 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

回答by Roman Pekar

If you don't want to list all your columns in CTE, another way to do this would be to use outer apply:

如果您不想在 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

回答by Pascal

The most effective way to do it without repeating your code is use of HAVINGinstead of WHERE

不重复代码的最有效方法是使用HAVING而不是WHERE

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

回答by Shekhar Joshi

How about using a subquery(this worked for me in Mysql)?

使用子查询怎么样（这在 Mysql 中对我有用）？

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

回答by roier.rdz

HAVING works in MySQL according to documentation:

根据文档，HAVING 在 MySQL 中工作：

The HAVINGclause was added to SQL because the WHERE keyword could not be used with aggregate functions.

在HAVING子句被添加到SQL，因为关键字无法与合计函数使用的地方。

回答by Lukasz Szozda

You could refer to column alias but you need to define it using CROSS/OUTER APPLY:

您可以引用列别名，但您需要使用CROSS/OUTER APPLY以下方法定义它：

SELECT s.logcount, s.logUserID, s.maxlogtm, c.daysdiff
FROM statslogsummary s
CROSS APPLY (SELECT DATEDIFF(day, s.maxlogtm, GETDATE()) AS daysdiff) c
WHERE c.daysdiff > 120;

DBFiddle Demo

DBFiddle 演示

Pros:

优点：

• single definition of expression(easier to maintain/no need of copying-paste)
• no need for wrapping entire query with CTE/outerquery
• possibility to refer in WHERE/GROUP BY/ORDER BY
• possible better performance(single execution)

• 表达式的单一定义（更易于维护/无需复制粘贴）
• 不需要用 CTE/outerquery 包装整个查询
• 参考的可能性 WHERE/GROUP BY/ORDER BY
• 可能更好的性能（单次执行）

回答by Scy

Came here looking something similar to that, but with a CASE WHEN, and ended using the where like this: WHERE (CASE WHEN COLUMN1=COLUMN2 THEN '1' ELSE '0' END) = 0maybe you could use DATEDIFFin the WHEREdirectly. Something like:

来到这里看起来与此类似，但使用 CASE WHEN，并以这样的方式结束使用 where：WHERE (CASE WHEN COLUMN1=COLUMN2 THEN '1' ELSE '0' END) = 0也许您可以直接使用DATEDIFFin WHERE。就像是：

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