select model1.y, model1.m, model1.d, avg(model1.sus) from(
select year(model.createTime) as y, month(model.createTime) as m, day(model.createTime) as d, model.sustainedTime as sus from SessionVisitor model
where model.createTime >= ? and model.createTime <= ?
group by y, m, d, sus
) model1 group by model1.y, model1.m, model1.d order by avg(model1.sus)上面是一个hql语句,SessionVisitor是一个实体,createTime开始时间,
sustainedTime 持续时间,year(),month(),day()是mysql数据库自带的函数,例如:year(model.createTime)就是求出这个时间的年字段。这个语句有错误,请大家帮我看看哪里错了。急
select year(model.createTime) as y, month(model.createTime) as m, day(model.createTime) as d, model.sustainedTime as sus from SessionVisitor model
where model.createTime >= ? and model.createTime <= ?
group by y, m, d, sus
) model1 group by model1.y, model1.m, model1.d order by avg(model1.sus)上面是一个hql语句,SessionVisitor是一个实体,createTime开始时间,
sustainedTime 持续时间,year(),month(),day()是mysql数据库自带的函数,例如:year(model.createTime)就是求出这个时间的年字段。这个语句有错误,请大家帮我看看哪里错了。急
create table SessionVisitor(
createTime datetime,
sustainedTime datetime
);插入数据后如下,
+---------------------+---------------------+
| createTime | sustainedTime |
+---------------------+---------------------+
| 1989-01-01 12:12:12 | 1990-01-01 12:12:12 |
| 1991-01-02 10:10:10 | 1992-09-08 10:10:10 |
| 1993-04-03 10:10:10 | 1994-09-03 12:13:12 |
+---------------------+---------------------+然后如下查询,
select model1.y, model1.m, model1.d, avg(model1.sus) from(
select year(model.createTime) as y, month(model.createTime) as m, day(model.createTime) as d, model.sustainedTime as sus from SessionVisitor model
where model.createTime >='1000-01-01 00:00:00' and model.createTime <= '2020-12-12 00:00:00'
group by y, m, d, sus
) model1 group by model1.y, model1.m, model1.d order by avg(model1.sus);得到以下结果:
+------+------+------+---------------------------+
| y | m | d | avg(model1.sus) |
+------+------+------+---------------------------+
| 1989 | 1 | 1 | 19900101121212.0000000000 |
| 1991 | 1 | 2 | 19920908101010.0000000000 |
| 1993 | 4 | 3 | 19940903121312.0000000000 |
+------+------+------+---------------------------+证明你的查询语句没有错,可能你的datetime格式表达错误.