在Postgresql中的where子句中使用别名列
发布时间:2020-12-13 16:47:10 所属栏目:百科 来源:网络整理
导读:我有一个这样的查询: SELECT jobs.*,(CASE WHEN lead_informations.state IS NOT NULL THEN lead_informations.state ELSE 'NEW' END) as lead_stateFROM "jobs"LEFT JOIN lead_informations ON lead_informations.job_id = jobs.id AND lead_informations.
我有一个这样的查询:
SELECT jobs.*,(CASE WHEN lead_informations.state IS NOT NULL THEN lead_informations.state ELSE 'NEW' END) as lead_state FROM "jobs" LEFT JOIN lead_informations ON lead_informations.job_id = jobs.id AND lead_informations.mechanic_id = 3 WHERE (lead_state = 'NEW') 其中出现以下错误: PGError: ERROR: column "lead_state" does not exist LINE 1: ...s.id AND lead_informations.mechanic_id = 3 WHERE (lead_state... 在MySql这是有效的,但显然不是在PostgreSQL。从我可以收集,原因是查询的SELECT部分??的评估晚于WHERE部分。有这个问题的常见解决方法吗?
MySQL的支持是,因为你体验到,非标准。正确的方法是重新打印在SELECT子句中使用的同一个表达式:
SELECT jobs.*,CASE WHEN lead_informations.state IS NOT NULL THEN lead_informations.state ELSE 'NEW' END as lead_state FROM "jobs" LEFT JOIN lead_informations ON lead_informations.job_id = jobs.id AND lead_informations.mechanic_id = 3 WHERE lead_informations.state IS NULL (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |