找到你要的答案

Q:Using an Alias column in the where clause in Postgresql

Q:使用别名列在WHERE子句在PostgreSQL

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?

我有这样的查询:

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。从我所能收集到的,原因是查询的选择部分比所述部分的后期求值。有这一问题的常用方法?

answer1: 回答1:

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

MySQL的支持,你有经验,非标。正确的方法是转载选择子句中使用的相同表达式:

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
answer2: 回答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.

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

我挣扎着同样的问题,“MySQL语法不规范”是不是在我看来,一个有效的论据。PostgreSQL添加方便非标准扩展为好,比如“插入…返回“获取插入后的自动ID。此外,重复大的查询不是一个优雅的解决方案。

不过,我发现声明很有帮助。它在查询中创建一个临时视图,可以像通常的表那样使用。我不知道我是否重写了你的连接正确,但一般来说,应该这样工作:

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'
answer3: 回答3:

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

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

您需要在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')
answer4: 回答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子句一无所知。

answer5: 回答5:

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

我用别名在这样的地方。(内部查询)。

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
sql  postgresql  alias