SELECT logcount, logUserID, maxlogtm
, DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE daysdiff > 120
我明白了
"无效列名daysdiff".
Maxlogtm是一个日期时间字段.正是这些小事让我发疯.
SELECT logcount, logUserID, maxlogtm
, DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE daysdiff > 120
我明白了
"无效列名daysdiff".
Maxlogtm是一个日期时间字段.正是这些小事让我发疯.
SELECT
logcount, logUserID, maxlogtm,
DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE ( DATEDIFF(day, maxlogtm, GETDATE() > 120)
通常情况下,在WHERE
子句中不能引用字段别名.(将其视为整个SELECT
(包括别名)应用于WHERE
子句之后.)
但是,正如其他答案中提到的,可以强制SQL在WHERE
子句之前处理SELECT
.通常使用括号强制执行逻辑操作顺序,或使用公共表表达式(CTE):
括号/子 Select :
SELECT
*
FROM
(
SELECT
logcount, logUserID, maxlogtm,
DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
) as innerTable
WHERE daysdiff > 120
或者看看亚当的答案,看看CTE版本的答案.