Referring to a Column Alias in a WHERE Clause

问题:

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.

回答1:

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

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

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.

回答2:

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

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

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:

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

回答7:

You could refer to column alias but you need to define it using 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

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)