SQL PostgreSQL Where计数条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8119489/
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
PostgreSQL Where count condition
提问by Hadi
I have following query in PostgreSQL:
我在 PostgreSQL 中有以下查询：
SELECT
COUNT(a.log_id) AS overall_count
FROM
"Log" as a,
"License" as b
WHERE
a.license_id=7
AND
a.license_id=b.license_id
AND
b.limit_call > overall_count
GROUP BY
a.license_id;
Why do I get this error:
为什么我会收到此错误：
ERROR: column "overall_count" does not exist
错误：“overall_count”列不存在
My table structure:
我的表结构：
License(license_id, license_name, limit_call, create_date, expire_date)
Log(log_id, license_id, log, call_date)
I want to check if a license has reached the limit for calls in a specific month.
我想检查许可证是否已达到特定月份的呼叫限制。
回答by Erwin Brandstetter
SELECT a.license_id, a.limit_call
, count(b.license_id) AS overall_count
FROM "License" a
LEFT JOIN "Log" b USING (license_id)
WHERE a.license_id = 7
GROUP BY a.license_id -- , a.limit_call -- add in old versions
HAVING a.limit_call > count(b.license_id)
Major points
要点
In versions prior to PostgreSQL 9.1 you have to add
limit_callto theGROUP BYclause. Beginning with version 9.1 it is enough to have the primary key in theGROUP BYclause. The release notes for 9.1:Allow non-
GROUP BYcolumns in the query target list when the primary key is specified in theGROUP BYclauseYour
WHEREcondition has to move to theHAVINGclause since it refers to the result of an aggregate function. And you cannot refer to output columns(column aliases) in theHAVINGclause, where you can only reference input columns. So you have to repeat the expression. Per documentation:An output column's name can be used to refer to the column's value in
ORDER BYandGROUP BYclauses, but not in theWHEREorHAVINGclauses; there you must write out the expression instead.I reversed the order of tables in the
FROMclause and cleaned up the syntax a bit to make it less confusing.USINGis just a notational convenience here.I used
LEFT JOINinstead ofJOIN, so you do not exclude licenses without any logs at all.I would advise notto use mixed case identifiersin Postgres if possible. Very error prone.
Only non-null values are counted by
count(). Since you want to count related entriesin table"Log"it is safer and slightly cheaper to usecount(b.license_id). This column is used in the join, so we don't have to bother whether the column can be null or not.count(*)is even shorter and slightly faster, yet. If you don't mind to get a count of1for0rows in the left table, use that.
在 PostgreSQL 9.1 之前的版本中，您必须添加
limit_call到GROUP BY子句中。从 9.1 版开始，GROUP BY子句中有主键就足够了。9.1的发行说明：GROUP BY当GROUP BY子句中指定主键时，允许查询目标列表中的非列您的
WHERE条件必须移至HAVING子句，因为它指的是聚合函数的结果。并且您不能在子句中引用输出列（列别名）HAVING，您只能在其中引用输入列。所以你必须重复这个表达式。根据文档：输出列的名称可用于在
ORDER BYandGROUP BY子句中引用列的值，但不能在WHEREorHAVING子句中引用 ；在那里你必须写出表达式。我颠倒了
FROM子句中表的顺序并稍微清理了语法以减少混乱。USING在这里只是一个符号方便。我使用了
LEFT JOIN代替JOIN，因此您根本不会排除没有任何日志的许可证。如果可能，我建议不要在 Postgres 中使用混合大小写标识符。非常容易出错。
仅计算非空值
count()。由于您想计算表中的相关条目，"Log"因此使用count(b.license_id). 此列用于连接中，因此我们不必担心该列是否可以为空。count(*)甚至更短，速度也更快。如果您不介意获取左表中1的0行数，请使用它。
回答by Hadi
The wherequery doesn't recognize your column alias, and furthermore, you're trying to filter out rows afteraggregation. Try:
该where查询不认识你列的别名，而且，你要过滤掉行后聚集。尝试：
SELECT
COUNT(a.log_id) AS overall_count
FROM
"Log" as a,
"License" as b
WHERE
a.license_id=7
AND
a.license_id=b.license_id
GROUP BY
a.license_id
having b.limit_call > count(a.log_id);
The havingclause is similar to the whereclause, except that it deals with columns after an aggregation, whereas the whereclause works on columns before an aggregation.
该having子句与子句类似where，不同之处在于它处理聚合后的列，而该where子句处理聚合前的列。
Also, is there a reason why your table names are enclosed in double quotes?
另外，您的表名是否有理由用双引号括起来？
