java - hibernate 问题 : must appear in the GROUP BY clause or be used in an aggregate function

Question

我要运行的查询:

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 运行该查询时，我抛出了这个错误:

ERROR: column "useractivi0_.activity_date" 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),***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

我该如何解决这个问题？

当我从我的 toad shell 直接在数据库上运行第一个查询时，它似乎工作得很好。但是，当我使用 Hibernate 从代码运行相同的查询时，我得到了那个错误。

Answer 1

您可以尝试在子查询中使用 sum 和 date_trunc，如下所示:

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

再次在外部查询中进行分组。看看是否有帮助。