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

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 SELECT part of the query is evaluated later than the WHERE part. Is there a common workaround for this problem?

在MySql中，这是有效的，但显然不是Postgresql。根据我所收集的信息，原因是查询的SELECT部分比WHERE部分要晚。对于这个问题是否有一个共同的解决办法?

5 个解决方案

#1

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

#2

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添加方便非标准扩展,例如“插入……返回…"在插入之后获取自动id。另外,重复查询不是一个优雅的解决方案。

However, I found the WITH statement very 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语句非常有用。它在查询中创建了一个临时视图，您可以像通常的表那样使用它。我不确定我是否正确地重写了你的连接，但总的来说它应该是这样的:

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'

#3

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

#4

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.

我认为常见的解决方案是为计算使用内部选择(在本例中为CASE语句)，以便在执行到该查询时，整个外部查询都可以使用内部选择的结果。否则，WHERE子句将首先被求值，并且对SELECT子句一无所知。

#5

-1

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

#1