在 Postgresql 的 where 子句中使用 Alias 列

我有一个问题是这样的:

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部分的计算晚。对于这个问题有没有通用的解决方案？

63713 次浏览
小开
最佳答案

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
小开

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.

小开

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 (CTE's). 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'
小开

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

I used alias in where like this. (Sub 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
小开

Subquery:

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

提交答案