在Postgresql的where子句中使用Alias列
技术问答
153 人阅读
|
0 人回复
|
2023-09-13
|
我有这样的查询:
' t! z( T! S1 y+ E% h5 L- c T0 `* RSELECT
/ Q" {& `. g! [+ f; J1 @/ v) s jobs.*, ) y' |, Q% Z' T% H, c( @7 Q5 F c
(
4 A; ]: q- _% V9 m4 r CASE
+ d F4 k6 v+ d4 p6 W0 [ WHEN lead_informations.state IS NOT NULL THEN lead_informations.state" [% Y% t' r: o0 z, h, [. u9 x1 c, w
ELSE 'NEW'
1 b# ~" [) e4 {0 B# D END
# L" O, n; M; b: L# x5 s ) AS lead_state4 {$ F0 q3 m) Y" |7 g$ D# k! P
FROM
; y) L; ?5 f% W, O' V1 |- D jobs
9 q2 ~. B* ^* ]* w LEFT JOIN lead_informations ON
/ `: U* [; {: B' B( ^. E$ I lead_informations.job_id = jobs.id
L/ ]! J1 P6 A- j! L/ i* P- h/ n AND4 f6 z5 z T* J* }, |$ ]
lead_informations.mechanic_id = 3. I6 s+ K/ u; M& {9 E: P' f" I
WHERE" Q3 S9 s& I9 L4 o
lead_state = 'NEW'
J: n& r& S, \8 h0 M出现以下错误:
+ d# }' o" O4 A" _) P0 ?( L7 @PGError: ERROR: column "lead_state" does not exist
/ e7 A ?# e: ], gLINE 1: ...s.id AND lead_informations.mechanic_id = 3 WHERE (lead_state...6 X; ]& O& H" k$ [7 x
在MySql中,这是有效的,但显然在Postgresql中是无效的。据我所知,原因是SELECT查询部分的评估晚于该WHERE部分。是否有解决此问题的常用解决方法? u- z2 b3 G3 l; i6 A. t
3 O6 Q, S! R% S) z: s1 i+ k解决方案:
, a s& N+ b; g( k; L 1 }+ |, K, S$ _8 _1 r
& }& |1 _. g" h$ y, n+ ~* |, W
) c% s" V5 M& s# o' l 正如您所经历的那样,MySQL的支持是非标准的。正确的方法是重新打印SELECT子句中使用的相同表达式:7 z8 o+ s: P- V& H: Z# D7 |/ E
SELECT3 t- U2 { g& p' r$ N
jobs.*,
# @5 @3 H- C- _2 r0 F$ J# o5 l CASE
3 h! U5 ~) n9 ]( P* e8 q+ n WHEN lead_informations.state IS NOT NULL THEN lead_informations.state
) e9 L* s9 g* \, c3 f- Z ELSE 'NEW' ( u0 g% E3 I; M* x5 h2 y* B
END AS lead_state% {) d9 N3 A: G! s
FROM
( E( N! }, |; j jobs% c( C' F# G. l' y$ s
LEFT JOIN lead_informations ON
/ }1 L0 F/ R& X6 i lead_informations.job_id = jobs.id
2 b1 I9 G2 h, k AND, h: {% \1 \* l* m0 F0 a/ f6 s
lead_informations.mechanic_id = 3
& L8 e. v- q3 }9 |' t9 S/ C l9 jWHERE4 H+ M+ ^9 \2 l1 Q/ I
lead_informations.state IS NULL |
|
|
|
|
|