在Postgresql的where子句中使用Alias列 [英] Using an Alias column in the where clause in Postgresql

问题描述

我有这样的查询:

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部分要晚评估.是否有解决此问题的常用解决方法?

In MySql this is valid, but apparently not in Postgresql. From what I can gather, the reason is that the SELECT part of the query is evaluated later than the WHERE part. Is there a common workaround for this problem?

推荐答案

正如您所经历的那样，MySQL的支持是非标准的.正确的方法是重新打印SELECT子句中使用的相同表达式:

MySQL's support is, as you experienced, non-standard. The correct way is to reprint the same expression used in the SELECT clause:

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

这篇关于在Postgresql的where子句中使用Alias列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！