PostgreSQL凡计数条件 [英] PostgreSQL Where count condition

问题描述

  SELECT 
 COUNT（a.log_id）AS overall_count 
 FROM 
Logas a，
Licenseas b 
 WHERE 
 a.license_id = 7 
 AND 
 a.license_id = b。 license_id 
 AND 
 b.limit_call> overall_count 
 GROUP BY 
 a.license_id; 
  

为什么会出现此错误：

错误：列overall_count不存在

我的表结构：

pre $ 许可证（license_id，license_name，limit_call，create_date，expire_date）
Log（log_id，license_id，log，call_date）

我想检查许可是否已达到特定月份的电话限制。

<您可以通过以下方式选择您的帐户：$ a $ _ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ FROMLicensea
LEFT JOINLogb USING（license_id）
WHERE a.license_id = 7
GROUP BY a.license_id - ，a.limit_call - 在旧版本中添加
HAVING a.limit_call> （b.license_id）

要点

• 在PostgreSQL 9.1以前的版本中，您必须将 limit_call 添加到 GROUP BY 子句。
  从版本9.1开始，只需在 GROUP BY 子句中使用主键。 9.1版发布说明告诉我们：
  
  

  
  

  允许查询目标列表中的非$ GROUP BY 列在主$ b $在 GROUP BY 子句中指定b键
  




• code> WHERE 条件必须移动到 HAVING 子句，因为它引用了聚合的结果功能。而且，您不能在 HAVING 子句中引用输出列（列别名），其中只能引用输入列。所以你必须重复表达。 根据文档：

  
  

  输出列的名称可用于引用
  中的列值ORDER BY 和 GROUP BY 子句，但不在 WHERE 或 HAVING 子句;
  您必须写出表达式。

  
  




• 我颠倒了 FROM 子句，并稍微清理一下语法，以减少混淆。 USING 在这里只是一个符号的便利。

> LEFT JOIN 而不是 JOIN ，因此您不会排除没有任何日志的许可证。



• 我建议不要使用 混合大小写标识符 （如果可能的话）。非常容易出错。




• 只有非空值由 count（）计数。由于您要在表格Log中统计相关条目，所以使用 count会更安全且更便宜（b.license_id） 即可。此列用于连接，因此我们不必担心列是否可以为null。
  
  count（*）会甚至更短，更快。如果在左表中为 0 行计数 1 无关紧要，请使用它。 / p>

I have following query in 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

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.

解决方案

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_call to the GROUP BY clause. Beginning with version 9.1 it is enough to have the primary key in the GROUP BY clause. The release notes for 9.1 tell us:

  Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause

• Your WHERE condition has to move to the HAVING clause since it refers to the result of an aggregate function. And you cannot refer to output columns (column aliases) in the HAVING clause, 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 BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

• I reversed the order of tables in the FROM clause and cleaned up the syntax a bit to make it less confusing. USING is just a notational convenience here.

• I used LEFT JOIN instead of JOIN, so you do not exclude licenses without any logs at all.

• I would advise not to use mixed case identifiers in Postgres if possible. Very error prone.

• Only non-null values are counted by count(). Since you want to count related entries in table "Log" it is safer and slightly cheaper to use count(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(*) would be even shorter and slightly faster, yet. If it doesn't matter to get a count of 1 for 0 rows in the left table, use that.

这篇关于PostgreSQL凡计数条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！