hibernate标准使用按小时分组

gmol1639  于 2021-06-25  发布在  Mysql
关注(0)|答案(1)|浏览(365)

所以我查询的参数只是日期范围。
示例参数为

startDateTime = '2018-01-10 00:00:00'
endDateTime   = '2018-01-11 00:00:00'

根据日期范围,我喜欢按时间范围对计数和金额进行分组, 0 - 23 应该是小时的值。
下面是我的代码。

dc.add(Restrictions.ge("date", startDateTime));
dc.add(Restrictions.lt("date", endDateTime));
dc.setProjection(
    Projections.projectionList()
        .add(Projections.alias(Projections.sum("amount"), "amount"))
        .add(
            Projections.sqlProjection(
                "Cast(Count(id) as Integer) count",
                new String[]{"count"},
                new Type[]{StandardBasicTypes.INTEGER})
        )
        .add(
            Projections.sqlProjection(
                "hour(date) as date",
                new String[]{"date"},
                new Type[]{StandardBasicTypes.INTEGER})
        )       
);

结果就是这样。

[
   {
     "amount": 0.1,
     "count": 1,
     "date": 3
   }, 
   {
     "amount": 0.3,
     "count": 1,
     "date": 3
   },
   {
     "amount": 1.5,
     "count": 1,
     "date": 11
   }, 
   {
     "amount": 2.2,
     "count": 1,
     "date": 11
   },
   {
     "amount": 0.6,
     "count": 1,
     "date": 11
   }
]

你知道怎么做吗?

rekjcdws

rekjcdws1#

你需要加上 groupProperty . 我不确定参数

dc.add(Restrictions.ge("date", startDateTime));
dc.add(Restrictions.lt("date", endDateTime));
dc.setProjection(
Projections.projectionList()
    .add(Projections.alias(Projections.sum("amount"), "amount"))
    .add(
        Projections.sqlProjection(
            "Cast(Count(id) as Integer) count",
            new String[]{"count"},
            new Type[]{StandardBasicTypes.INTEGER})
    )
    .add(
        Projections.groupProperty("hour(date)","date")
    )

);

相关问题