今天有一个需求:根据录入时间查询出近一周,近一月,近一年的数据
数据库如下:
这里使用到的SQL语句:
近一周:
select * from sqxx where TO_DAYS(now()) - TO_DAYS(bxsj) <=7 ;
近一月:
select * from sqxx where date_sub(curdate(), INTERVAL 30 DAY) <= date(bxsj) ;
近一年:
select * from sqxx where YEAR(bxsj)=YEAR(NOW());
然后可以借助三目运算符实现动态查找:
String sql =
time.equals("week")?"TO_DAYS(now()) - TO_DAYS(bxsj) <=7 ":(time.equals("month")?"date_sub(curdate(), INTERVAL 30 DAY) <=
date(bxsj)":"YEAR(bxsj)=YEAR(NOW())");
select * from sqxx where "+ sql ;
另外可以查询每个小时的数据:
FLOOR(MINUTE(add_time)/120),HOUR(add_time)
具体实例:
SELECT date_format(add_time,'%Y-%m-%d %H:%m:%s'), COUNT(id) count
FROM xx e
WHERE TO_DAYS(add_time) = TO_DAYS(date_sub(now(),interval #{time,jdbcType=INTEGER} day))
GROUP BY FLOOR(MINUTE(add_time)/120),HOUR(add_time)
ORDER BY Hour(add_time);
大功告成!!!