postgres - SELECT / GROUP BY - 列中的部分列必须出现在GROUP BY子句中或用于聚合函数中 [英] postgres - partial column in SELECT/GROUP BY - column must appear in the GROUP BY clause or be used in an aggregate function

问题描述

以下两条语句在Postgres中产生一个错误：

  SELECT substring（start_time，1,8）AS date，从cdrs组按日期统计（*）为总数; 
 SELECT substring（start_time，1,8）AS date，count（*）作为来自cdrs group by substring的总数（start_time，1,8）; 
  

错误是：

列cdrs.start_time必须出现在GROUP BY子句中，或者在
集合函数中使用

我对postgres文档的阅读是，SELECT和GROUP BY都可以使用表达式
postgres 8.3 SELECT

start_time字段是一个字符串，其格式为ccyymmddHHMMSS 。在mySQL中，它们都产生了期望和预期的结果：

  + ---------- + ---- --- + 
 |日期|总| | 
 + ---------- + ------- + 
 | 20091028 | 9 | 
 | 20091029 | 110 | 
 | 20091120 | 14 | 
 | 20091121 | 4 | 
 + ---------- + ------- + 
 4行（0.00秒）
  

我需要坚持使用Postgres（heroku）。任何建议？

p.s。还有很多关于GROUP BY中缺少项目的讨论，以及为什么mySQL接受这个，为什么其他人不......严格遵守SQL规范等等，但我认为这与1062158/converting-mysql-select-to-postgresql 和 1769361 / postgresql-group-by-different-from-mysql 来保证一个单独的问题。

只是总结，错误

列cdrs.start_time 必须出现在GROUP BY子句中或用于集合函数中

是由 ORDER BY start_time 子句。完整的语句需要是：

  SELECT substring（start_time，1,8）AS date，count（*）as total FROM cdrs GROUP BY substring（start_time，1,8）ORDER BY substring（start_time，1,8）; 
  

或

  SELECT substring（start_time，1,8）AS日期，count（*）作为总数FROM cdrs GROUP BY日期ORDER BY日期; 
  

Both the following two statements produce an error in Postgres:

SELECT substring(start_time,1,8) AS date, count(*) as total from cdrs group by date;
SELECT substring(start_time,1,8) AS date, count(*) as total from cdrs group by substring(start_time,1,8);

The error is:

column "cdrs.start_time" must appear in the GROUP BY clause or be used in an aggregate function

My reading of postgres docs is that both SELECT and GROUP BY can use an expression postgres 8.3 SELECT

The start_time field is a string and has a date/time in form ccyymmddHHMMSS. In mySQL they both produce desired and expected results:

+----------+-------+
| date     | total |
+----------+-------+
| 20091028 |     9 |
| 20091029 |   110 |
| 20091120 |    14 |
| 20091121 |     4 |
+----------+-------+
4 rows in set (0.00 sec)

I need to stick with Postgres (heroku). Any suggestions?

p.s. there is lots of other discussion around that talks about missing items in GROUP BY and why mySQL accepts this, why others don't ... strict adherence to SQL spec etc etc, but I think this is sufficiently different to 1062158/converting-mysql-select-to-postgresql and 1769361/postgresql-group-by-different-from-mysql to warrant a separate question.

解决方案

Just to summarise, error

column "cdrs.start_time" must appear in the GROUP BY clause or be used in an aggregate function

was caused (in this case) by ORDER BY start_time clause. Full statement needed to be either:

SELECT substring(start_time,1,8) AS date, count(*) as total FROM cdrs GROUP BY substring(start_time,1,8) ORDER BY substring(start_time,1,8);

or

    SELECT substring(start_time,1,8) AS date, count(*) as total FROM cdrs GROUP BY date ORDER BY date;

这篇关于postgres - SELECT / GROUP BY - 列中的部分列必须出现在GROUP BY子句中或用于聚合函数中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！