在Postgresql的where子句中使用Alias列
技术问答
160 人阅读
|
0 人回复
|
2023-09-13
|
我有这样的查询:
! w2 c/ X: T( }* F% t$ YSELECT5 ]- N' G- z0 A) R: L5 B
jobs.*,
% y$ f' h# U! c" ` (: e8 M+ E: u. N0 h2 ^. O; y2 L
CASE
- }" j: g) I* l+ I: @ WHEN lead_informations.state IS NOT NULL THEN lead_informations.state( |6 E& C6 M2 N+ j s2 Z, b3 d
ELSE 'NEW'
% w5 x7 R8 X& v @7 T, J, E END
7 J4 C/ F m% y( S7 u ) AS lead_state& r) r) _/ v1 H1 a( v9 a
FROM- R7 _0 p( j" z+ B8 ~
jobs
0 X) R T' k) J. g; W( p1 V LEFT JOIN lead_informations ON
9 G; j* }& n+ v: k& ~# g- H+ o: _ lead_informations.job_id = jobs.id7 E+ C) @; j$ z7 j0 L/ P6 Q6 b+ y
AND3 B, s: d& C. D) M: ]
lead_informations.mechanic_id = 3
$ B8 _4 O8 @ x+ {) ^7 uWHERE5 S0 x. c/ U# b" P4 a
lead_state = 'NEW'- A7 b1 h8 e. b3 O+ U7 s
出现以下错误:% @# v7 X+ A" O9 R$ u u! X
PGError: ERROR: column "lead_state" does not exist
! o, p" N# ] B$ M G( T! b2 LLINE 1: ...s.id AND lead_informations.mechanic_id = 3 WHERE (lead_state...( n$ C- u+ P7 q$ O/ |
在MySql中,这是有效的,但显然在Postgresql中是无效的。据我所知,原因是SELECT查询部分的评估晚于该WHERE部分。是否有解决此问题的常用解决方法?
) ?& }" x/ l: a& _# M : s i R& F) W3 A; ?( l1 S
解决方案:$ s# P0 r* a# C; L4 y7 B
, P' R* m5 P1 ^, U$ Y- e$ ~9 |0 f8 j$ ]+ T/ w& ^! T
( u- E" s, P$ f, ] 正如您所经历的那样,MySQL的支持是非标准的。正确的方法是重新打印SELECT子句中使用的相同表达式:
' _% d e* ]/ A, v' U7 Z M. eSELECT
# C+ @8 q7 t8 h; K, k jobs.*, 7 l5 ^0 D" | `7 R: B& S
CASE : G- a/ g( y+ R& k$ K
WHEN lead_informations.state IS NOT NULL THEN lead_informations.state * I8 O# n% x, L% R7 J- f+ d
ELSE 'NEW'
. d$ Z1 Q; `) Y2 D END AS lead_state y( W4 ^& W* W
FROM
5 \( [ W! ]% H! l8 w/ n jobs
9 `9 E+ g0 k! \( M4 |3 [; n t LEFT JOIN lead_informations ON' |1 E' V9 ~& n& V4 ?. f3 w2 e5 B
lead_informations.job_id = jobs.id
, D, k! c; M4 S AND
" Y* z l: [3 V! z; i* ^ lead_informations.mechanic_id = 3
$ N: {0 U9 Q4 [+ NWHERE
" f9 w0 W3 G9 ~5 H7 b lead_informations.state IS NULL |
|
|
|
|
|