postgresql - PostgreSQL 中的 HAVING 子句

Question

我正在将 MySQL 查询重写为 PostgreSQL。我有一个包含文章的表格和另一个包含类别的表格。我需要选择所有类别，其中至少有 1 篇文章:

SELECT c.*,(
    SELECT COUNT(*) 
    FROM articles a 
    WHERE a."active"=TRUE AND a."category_id"=c."id") "count_articles" 
FROM articles_categories c 
HAVING (
    SELECT COUNT(*) 
    FROM articles a 
    WHERE a."active"=TRUE AND a."category_id"=c."id" ) > 0

我不知道为什么，但是这个查询导致了一个错误:

ERROR:  column "c.id" must appear in the GROUP BY clause or be used in an aggregate function at character 8

Answer 1

HAVING 子句有点难以理解。我不确定 MySQL 如何解释它。但是可以在这里找到 Postgres 文档:

http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-HAVING

它本质上说:

The presence of HAVING turns a query into a grouped query even if there is no GROUP BY clause. This is the same as what happens when the query contains aggregate functions but no GROUP BY clause. All the selected rows are considered to form a single group, and the SELECT list and HAVING clause can only reference table columns from within aggregate functions. Such a query will emit a single row if the HAVING condition is true, zero rows if it is not true.

The same is also explained in this blog post ，它显示了没有 GROUP BY 的 HAVING 如何隐式暗示 SQL:1999 标准“总计”，即 GROUP BY ( ) 子句(其中PostgreSQL 不支持)

由于您似乎不想要单行，HAVING 子句可能不是最佳选择。

考虑到您的实际查询和要求，只需重写整个内容并将JOIN articles_categories 转换为articles:

SELECT DISTINCT c.*
FROM articles_categories c
JOIN articles a 
ON a.active = TRUE 
AND a.category_id = c.id

备选方案:

SELECT *
FROM articles_categories c
WHERE EXISTS (SELECT 1 
                FROM articles a
               WHERE a.active = TRUE 
                 AND a.category_id = c.id)