PostgreSQL 中的 HAVING 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5496786/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
HAVING clause in PostgreSQL
提问by Radek Simko
I'm rewriting the MySQL queries to PostgreSQL. I have table with articles and another table with categories. I need to select all categories, which has at least 1 article:
我正在将 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
I don't know why, but this query is causing an error:
我不知道为什么，但是这个查询导致了一个错误：
ERROR: column "c.id" must appear in the GROUP BY clause or be used in an aggregate function at character 8
回答by Lukas Eder
The HAVINGclause is a bit tricky to understand. I'm not sure about how MySQL interprets it. But the Postgres documentation can be found here:
该HAVING条款有点难以理解。我不确定 MySQL 如何解释它。但是可以在此处找到 Postgres 文档：
http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-HAVING
http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-HAVING
It essentially says:
它基本上说：
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.
即使没有 GROUP BY 子句，HAVING 的存在也会将查询转换为分组查询。这与查询包含聚合函数但没有 GROUP BY 子句时发生的情况相同。所有选定的行都被视为形成一个组，并且 SELECT 列表和 HAVING 子句只能从聚合函数中引用表列。如果 HAVING 条件为真，则此类查询将发出单行，如果不为真，则将发出零行。
The same is also explained in this blog post, which shows how HAVINGwithout GROUP BYimplicitly implies a SQL:1999 standard "grand total", i.e. a GROUP BY ( )clause (which isn't supported in PostgreSQL)
这篇博文中也有同样的解释，它展示了如何HAVING没有GROUP BY隐含地暗示 SQL:1999 标准的“总计”，即一个GROUP BY ( )子句（PostgreSQL 不支持）
Since you don't seem to want a single row, the HAVINGclause might not be the best choice.
由于您似乎不想要单行，因此该HAVING子句可能不是最佳选择。
Considering your actual query and your requirement, just rewrite the whole thing and JOINarticles_categoriesto articles:
考虑到你的实际查询和您的要求，只是重写了整个事情，并JOINarticles_categories到articles：
SELECT DISTINCT c.*
FROM articles_categories c
JOIN articles a
ON a.active = TRUE
AND a.category_id = c.id
alternative:
选择：
SELECT *
FROM articles_categories c
WHERE EXISTS (SELECT 1
FROM articles a
WHERE a.active = TRUE
AND a.category_id = c.id)
回答by SauZheR
SELECT * FROM categories c
WHERE
EXISTS (SELECT 1 FROM article a WHERE c.id = a.category_id);
should be fine... perhaps simpler ;)
应该没问题……也许更简单；）
