引用WHERE子句中的列别名。

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

I get "invalid column name daysdiff". Maxlogtm is a datetime field. It's the little stuff that drives me crazy.

我得到了“无效的列名daysdiff”。Maxlogtm是一个datetime字段。正是这些小事让我发疯。

6 个解决方案

#1

117

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 WHERE clause. (Think of it as the entire SELECT including aliases, is applied after the WHERE clause.)

通常，您不能引用WHERE子句中的字段别名。(请将其视为包括别名在内的整个选择，适用于WHERE子句。)

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):

但是，正如在其他答案中提到的，您可以强制SQL在WHERE子句之前处理SELECT。这通常使用括号来强制执行逻辑操作顺序或使用公共表表达式(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版本。

#2

If you want to use the alias in your WHERE clause, you need to wrap it in a sub select, or CTE:

如果您想在WHERE子句中使用别名，需要将其封装到一个sub select中，或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

#3

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

如果你不想把所有列都列在CTE中，另一种方法是使用外部应用:

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

#4

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

#5

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

在不重复代码的情况下，最有效的方法是使用“拥有”而不是“在哪里”

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

#6

HAVING works in MySQL according to documentation:

根据文件在MySQL工作:

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

之所以将have子句添加到SQL，是因为WHERE关键字不能与聚合函数一起使用。

#1

117

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