PostgreSQL中如何使用子查询结果列过滤整体查询结果

SQL语句的执行顺序是先执行WHERE子句，再执行SELECT子句。当你在WHERE里引用highest_offer这个别名时，该别名还没被SELECT子句创建，因此数据库会提示“列不存在”的错误。

以下是三种可行的解决方法：

方案一：在WHERE中重复子查询逻辑 #

直接将生成highest_offer的子查询放到WHERE条件里，注意price是数值类型，无需加单引号：

SELECT c1.id AS id,
       c1.title AS title,
       (
           SELECT b1.price
           FROM bids b1
           WHERE b1.product = c1.id
           ORDER BY b1.price DESC
           LIMIT 1
       ) AS highest_offer
FROM products c1
WHERE (
    SELECT b1.price
    FROM bids b1
    WHERE b1.product = c1.id
    ORDER BY b1.price DESC
    LIMIT 1
) = 538.16
ORDER BY highest_offer

方案二：用子查询/CTE先生成含别名的结果集 #

先通过子查询或CTE生成包含highest_offer的中间结果，再在外层进行过滤：

子查询写法 #

SELECT *
FROM (
    SELECT c1.id AS id,
           c1.title AS title,
           (
               SELECT b1.price
               FROM bids b1
               WHERE b1.product = c1.id
               ORDER BY b1.price DESC
               LIMIT 1
           ) AS highest_offer
    FROM products c1
) AS product_with_highest_offer
WHERE highest_offer = 538.16
ORDER BY highest_offer

CTE写法（适用于PostgreSQL、MySQL 8.0+等支持CTE的数据库） #

WITH product_with_highest_offer AS (
    SELECT c1.id AS id,
           c1.title AS title,
           (
               SELECT b1.price
               FROM bids b1
               WHERE b1.product = c1.id
               ORDER BY b1.price DESC
               LIMIT 1
           ) AS highest_offer
    FROM products c1
)
SELECT *
FROM product_with_highest_offer
WHERE highest_offer = 538.16
ORDER BY highest_offer

方案三：改用JOIN方式获取最高出价（性能更优） #

用MAX(price)结合分组查询替代子查询，逻辑等价且数据量大时性能更好：

SELECT c1.id AS id,
       c1.title AS title,
       b1.price AS highest_offer
FROM products c1
JOIN (
    SELECT product, MAX(price) AS price
    FROM bids
    GROUP BY product
) AS b1 ON c1.id = b1.product
WHERE b1.price = 538.16
ORDER BY highest_offer

内容的提问来源于stack exchange，提问作者UAhmadSoft