适用于MySQL的GROUP BY查询被PostgreSQL拒绝 [英] GROUP BY query that works in MySQL is rejected by PostgreSQL

问题描述

我有这个查询（在MySQL中工作正常）：

 选择单位。*，activity_logs.created_at 
 FROM单位左加入unit.id = activity_logs.unit_id 
 GROUP BY units.id 
 ORDER BY activity_logs.created_at desc 
   
 
 
 Postgres给出此错误：
  PG :: Error：错误：列 activity_logs.created_at必须出现在GROUP BY子句中或在聚合函数
  
 I中使用知道Postgres在SQL方面是正确的，但是我如何得到想要的呢？那是与所有units表连接的每个unit的最新activity_log条目吗？
 
 
 
预先感谢！
 
 
 
 
 
 Jacob 
 
 
 
 
解决方案
错误说明了这一点：您需要使用聚合函数确定性，例如：
  SELECT个单位。*，max（activity_logs.created_at）
从单位左加入unit.id = activity_logs.unit_id上的activity_logs 
 GROUP BY units.id 
 ORDER BY max（activity_logs.created_at）desc; 
  
 
I have this query (that works fine in MySQL):
SELECT units.*, activity_logs.created_at 
FROM "units" left join activity_logs on units.id=activity_logs.unit_id 
GROUP BY units.id 
ORDER BY activity_logs.created_at desc
Postgres gives this error:
PG::Error: ERROR:  column "activity_logs.created_at" must appear in the GROUP BY clause or be used in an aggregate function
I know that Postgres is right SQL-wise but how do I get what I want? That is the latest activity_log entry for each unit joined with all of the units table?

Thanks in advance!


Jacob

解决方案
The error explains it: you need to use an aggregate function to make this deterministic, eg:
SELECT units.*, max(activity_logs.created_at)
FROM "units" left join activity_logs on units.id=activity_logs.unit_id 
GROUP BY units.id 
ORDER BY max(activity_logs.created_at) desc;


                        
这篇关于适用于MySQL的GROUP BY查询被PostgreSQL拒绝的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！