PostgreSQL中SELECT子查询列无法在WHERE子句使用的问题咨询

SQL执行有固定的优先级顺序，WHERE子句会在SELECT子句之前执行。当数据库处理WHERE里的count_receipts条件时，SELECT部分定义的这个别名还没生成，所以PostgreSQL会报“列不存在”的错误。MySQL做了语法兼容处理，允许在WHERE里用SELECT的别名，但这不符合SQL标准，PostgreSQL严格遵循标准，因此会触发报错。

方案1：用子查询包裹原结果 #

把原查询嵌套成子查询，在外层WHERE中过滤别名：

SELECT *
FROM (
    SELECT "organizations".*, (
        SELECT count(*) 
        FROM companies
        JOIN receipts ON receipts.company_id = companies.id
        WHERE companies.organization_id = organizations.id
        GROUP BY companies.organization_id
    ) AS count_receipts 
    FROM "organizations"
) AS org_with_count
WHERE count_receipts >= 10 
  AND count_receipts <= 20;

方案2：改用JOIN+聚合重写查询 #

这种写法避免了关联子查询的重复执行，性能通常更优：

SELECT o.*, COUNT(r.id) AS count_receipts
FROM "organizations" o
JOIN companies c ON c.organization_id = o.id
JOIN receipts r ON r.company_id = c.id
GROUP BY o.id -- PostgreSQL 10+版本中，若o.id是主键，可直接GROUP BY o.id，无需列出所有字段
HAVING COUNT(r.id) BETWEEN 10 AND 20;

注：如果是PostgreSQL 9.x及更早版本，GROUP BY需要包含organizations表的所有非聚合字段。

方案3：使用CTE（公共表表达式） #

逻辑和子查询一致，但可读性更强：

WITH org_with_count AS (
    SELECT "organizations".*, (
        SELECT count(*) 
        FROM companies
        JOIN receipts ON receipts.company_id = companies.id
        WHERE companies.organization_id = organizations.id
        GROUP BY companies.organization_id
    ) AS count_receipts 
    FROM "organizations"
)
SELECT *
FROM org_with_count
WHERE count_receipts >= 10 
  AND count_receipts <= 20;

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