时间戳

首页 > 解决方案 > postgresql 错误分组和聚合错误

postgresql - postgresql 错误分组和聚合错误

问题描述

我有一个代码可用于我的表格按航班日期计算下载次数和每天的注册次数。它给出了这个错误

SELECT  "public".aircraft.aircraft_id, COUNT(*) AS TOTALDOWNLOAD, "public".aircraft.register_number, to_char("public".flight.flight_date, 'DDMMYYYY') as flight_date , to_char("public".flight.health_start_date, 'hh24:mi') as health_start_date, to_char("public".flight.health_end_date, 'hh24:mi') as health_end_date,  "public".flight.maintenance_flight_time 
FROM  "public".flight  

INNER JOIN   "public".aircraft
ON
"public".flight.aircraft_id = "public".aircraft.id
WHERE "public".flight.maintenance_flight_time > 0 
Group by "public".flight.flight_date, "public".aircraft.register_number

错误详情如下：

ERROR [42803] ERROR: column "aircraft.aircraft_id" must appear in the GROUP BY clause or be used in an aggregate function;
Error while executing the query (PSQLODBC35W.DLL)

标签： postgresqlodbc

解决方案

GROUP BYs 有点棘手。由于您只对 COUNT 感兴趣，因此我会减少查询以删除您不会使用的所有参数。如果您考虑一下，如果您按航班日期分组，那么数据库应该为其他一些字段返回什么 - 例如 health_start_date？它不知道该怎么做，所以它会抛出一个错误。

从这个精简版开始。我不确定 WHERE 子句是否会引起麻烦。您可能需要 GROUP BY flight_date 别名而不是“public”.flight.flight_date。如果"public".flight.flight_date是时间戳，您肯定必须对别名进行 GROUP BY。根据这些表的大小，执行 to_char 转换可能会很慢。使用date("public".flight.flight_date可能会更快。

SELECT to_char("public".flight.flight_date, 'DDMMYY') as "flight date",
       COUNT(*) AS TOTALDOWNLOAD, "public".aircraft.register_number
FROM  "public".flight  
INNER JOIN  "public".aircraft ON
            "public".flight.aircraft_id = "public".aircraft.id
WHERE "public".flight.maintenance_flight_time > 0 
GROUP BY "flight date", "public".aircraft.register_number
ORDER BY "flight date"

推荐阅读