在Postgresql的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

我在同一个问题上也很纠结,“mysql语法是非标准的”在我看来不是一个有效的论据。PostgreSQL还添加了方便的非标准扩展,例如“INSERT。。。返回…“以获取插入后的自动ID。而且,重复大型查询并不是一个优雅的解决方案。

然而,我发现 WITH statement 很有帮助。它在查询中创建了一个临时视图,您可以像使用普通表一样使用它。我不确定是否已正确重写了您的连接,但一般来说,它应该是这样工作的:

WITH jobs_refined AS (
    SELECT
        jobs.*,
        (SELECT 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
)
SELECT *
FROM jobs_refined
WHERE lead_state = 'NEW'

您需要复制where子句中的case语句,或者我倾向于执行以下操作:

SELECT *
FROM (
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
) q1
WHERE (lead_state = 'NEW')

我在这里用了别名(内部查询)。

Select "Vendors"."VendorId", "Vendors"."Name","Result"."Total" 
From (Select "Trans"."VendorId", ("Trans"."A"+"Trans"."B"+"Trans"."C")    AS "Total"
        FROM "Trans"
    WHERE "Trans"."Year"=2014                                                
    ) As "Result"
JOIN "Vendors" ON "Result"."VendorId"="Vendors"."VendorId" 
WHERE "Vendors"."Class"='I' AND "Result"."Total" > 200

SELECT "tab_1"."BirthDate", "tab_1"."col_1" FROM (
   SELECT BirthDate, DATEADD(year, 18, BirthDate) AS "col_1" FROM Employees
) AS "tab_1"
WHERE "tab_1"."col_1" >= '2000-12-31';