我正在将MySQL查询重写为PostgreSQL。我有一张包含文章的表和另一张包含类别的表。我需要选择所有至少有一篇文章的类别:
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
HAVING
在没有GROUP BY
也是合法的,并且 PostgreSQL 也支持。这与问题无关。 - Peter EisentrautGROUP BY
子句,则添加HAVING
子句会强制执行隐式的GROUP BY
:http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-HAVING。它还指出:“如果HAVING条件为真,则此类查询将发出单个行,如果不为真,则发出零行。”。所以这与问题有更多关系。你可以再次给我点赞 :-) - Lukas Eder