|
SELECT logcount, logUserID, maxlogtm+ r1 v" h4 e% t5 A
, DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff- j- E7 q; i/ Q7 q% x
FROM statslogsummary2 M! T R8 K: R0 r a" z8 S3 L' v
WHERE daysdiff > 120
, L+ m! n& e1 J' w% W# @! I$ f我懂了
0 K! A+ O M& }# J; _. H# Y2 p+ z1 p* C( t
“无效的列名daysdiff”。# q. B* I' g% x4 {
2 V5 x+ U' _! L# f" c% RMaxlogtm是日期时间字段。是让我发疯的小东西。
: F( Y ?6 }* O1 O$ q # ^; q( H9 \. t' V
解决方案:
+ R/ O0 |+ A6 P3 m
1 s' E6 \" @; p0 B; E5 e1 ~+ F
0 L+ n( d( c+ w% ]% I
! q: T2 L& _( i9 ?- u SELECT
1 c3 o$ }3 I/ F! W logcount, logUserID, maxlogtm,: l. C0 Q5 S- ~% ]
DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff8 q$ _ D5 a3 v* Z
FROM statslogsummary
; [$ X" U2 Z4 T: WWHERE ( DATEDIFF(day, maxlogtm, GETDATE() > 120)
6 v3 o: g0 ~2 I5 ^$ w6 X' Y; P通常,您不能在WHERE子句中引用字段别名。(将其视为SELECT包括别名在内的全部内容,在该WHERE子句之后应用。)
3 M( E. O% I2 G) r5 H; U& l但是,如其他答案所述,您可以强制将SQL视为SELECT在WHERE子句之前进行处理。通常使用圆括号来强制操作的逻辑顺序,或者使用通用表表达式(CTE)来完成此操作:
4 [# X- b2 t4 X) L) o括号/子选择:
) h6 z( v, g( }1 K% r; L n" \( xSELECT% i9 X1 C+ o4 K a3 Z3 m
*- Z( Z" `( u' W/ x* B$ g- ~
FROM
) n9 c# g: ?8 ~+ N# O8 v(* ~; m' G$ X( g" X/ n
SELECT3 `3 ^) G" {% i3 i: w. b) k2 J7 x
logcount, logUserID, maxlogtm,
1 }, `, L) W9 n" C DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff* z" J5 i, s+ {2 f8 J0 x8 [
FROM statslogsummary 4 A9 T) \5 l2 }
) as innerTable
1 j& t+ \; F( z# d- c; f9 M: PWHERE daysdiff > 1202 P7 I. f+ K1 F! A1 R, d' o& P
或参阅亚当的CTE版本答案。 |
|