休眠问题：必须出现在GROUP BY子句中或在聚合函数中使用 [英] Hibernate issue : must appear in the GROUP BY clause or be used in an aggregate function

问题描述

我要运行的查询

SELECT date_trunc('month',UA.activity_date),SUM(UA.points) FROM user_activity UA
WHERE UA.activity_date > '01/01/2014' AND UA.activity_date < '12/31/2014' 
GROUP BY date_trunc('month',UA.activity_date)
ORDER BY date_trunc('month',UA.activity_date)

预期结果：

03/01/2014 00:00:00 66.04000000000000000
05/01/2014 00:00:00 13.50000000000000000
07/01/2014 00:00:00 27.00000000000000000
08/01/2014 00:00:00 26.00000000000000000
09/01/2014 00:00:00 13.50000000000000000

当我使用Hibernate进行运行该查询，我将抛出此错误：

When I use Hibernate to run that query, I get this error thrown:

ERROR: column "useractivi0_.activity_date" must appear in the GROUP BY clause or be used in an aggregate function

中使用

要解决此问题，我将上面的查询修改为：

To fix that, I have modified the above query as :

SELECT date_trunc('month',UA.activity_date),SUM(UA.points) FROM user_activity UA
WHERE UA.activity_date > '01/01/2014' AND UA.activity_date < '12/31/2014' 
GROUP BY date_trunc('month',UA.activity_date),***UA.activity_date***
ORDER BY date_trunc('month',UA.activity_date)

但是，结果是错误的：

03/01/2014 00:00:00 25.40000000000000200
03/01/2014 00:00:00 25.40000000000000200
03/01/2014 00:00:00 15.24000000000000000
05/01/2014 00:00:00 13.50000000000000000
07/01/2014 00:00:00 9.00000000000000000
07/01/2014 00:00:00 18.00000000000000000
08/01/2014 00:00:00 4.50000000000000000

我该如何解决？

当我直接在db上从蟾蜍壳运行第一个查询时，它似乎工作正常。但是，当我使用Hibernate从代码中运行相同的查询时，会出现该错误。

When I run the first query from my toad shell directly on the db, it seems to work just fine. However, when I run the same query from code using Hibernate I get that error.

推荐答案

SELECT date_trunc('month',UA.activity_date) act_date,
       SUM(UA.points) point_sum 
FROM user_activity UA
WHERE UA.activity_date > '01/01/2014' AND UA.activity_date < '12/31/2014' 
GROUP BY act_date
ORDER BY act_date

这篇关于休眠问题：必须出现在GROUP BY子句中或在聚合函数中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！