SQL 在 Postgresql 的 where 子句中使用别名列

Using an Alias column in the where clause in Postgresql

提问by troelskn

I have a query like this:

我有一个这样的查询：

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'

Which gives the following error:

这给出了以下错误：

PGError: ERROR:  column "lead_state" does not exist
LINE 1: ...s.id AND lead_informations.mechanic_id = 3 WHERE (lead_state...

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

在 MySql 中这是有效的，但在 Postgresql 中显然不是。据我所知，原因是SELECT查询部分的评估晚于WHERE部分。是否有针对此问题的通用解决方法？

采纳答案by OMG Ponies

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

正如您所经历的，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

回答by Marten Lehmann

I struggled on the same issue and "mysql syntax is non-standard" is not a valid argument in my opinion. PostgreSQL adds handy non-standard extensions as well, for example "INSERT ... RETURNING ..." to get auto ids after inserts. Also, repeating large queries is not an elegant solution.

我在同样的问题上挣扎，“mysql 语法是非标准的”在我看来不是一个有效的论点。PostgreSQL 还添加了方便的非标准扩展，例如“INSERT ... RETURNING ...”以在插入后获取自动 ID。此外，重复大型查询不是一个优雅的解决方案。

However, I found the WITH statementvery helpful. It sort of creates a temporary view within the query which you can use like a usual table then. I'm not sure if I have rewritten your JOIN correctly, but in general it should work like this:

但是，我发现WITH 语句非常有帮助。它在查询中创建了一个临时视图，您可以像使用普通表一样使用它。我不确定我是否正确重写了您的 JOIN，但通常它应该像这样工作：

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'

回答by mrSpear

You would need to either duplicate the case statement in the where clause, or my preference is to do something like the following:

您需要在 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')

回答by David

I believe the common solution is to use an inner SELECT for the calculation (or CASE statement in this case) so that the result of the inner SELECT is available to the entire outer query by the time the execution gets to that query. Otherwise, the WHERE clause is evaluated first and knows nothing about the SELECT clause.

我相信常见的解决方案是使用内部 SELECT 进行计算（在本例中为 CASE 语句），以便在执行到达该查询时内部 SELECT 的结果可用于整个外部查询。否则，首先评估 WHERE 子句并且对 SELECT 子句一无所知。

回答by M Sohail Maroof

I used alias in where like this. (INNER Query).

我在这样的地方使用了别名。（内部查询）。

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

回答by Douglas Rosa

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';