MySQL按时间查找
RecentMutations表的结构如图,现在的需求是需要查找到2017年09月08日前10天的变体总数:
SQL语句:SELECT SUM(MutantNumber) FROM RecentMutations WHERE DATE_SUB( CURDATE() , INTERVAL 10 DAY) <= RecentMutations.`Date`;
执行的结果为:
MySQL关于根据日期查询数据
1.查询某日往前10天的数据:
SELECT * FROM RecentMutations WHERE DATE_SUB( CURDATE() , INTERVAL 10 DAY) <= RecentMutations.`Date`;
2.查询某段日期之间的数据:
SELECT * FROM RecentMutations WHERE RecentMutations.`Date` BETWEEN '2017-09-05' AND '2017-09-09';
3.查询某日往前三个月的数据:
SELECT * FROM RecentMutations WHERE DATE_SUB( CURDATE() , INTERVAL 3 MONTH) <= RecentMutations.`Date`;
4.查询从现在起往前三个月的数据:
SELECT * FROM RecentMutations WHERE RecentMutations.`Date` BETWEEN DATE_SUB( CURDATE() , INTERVAL 3 MONTH) AND NOW();
5.查询本月的数据:
SELECT * FROM RecentMutations WHERE DATE_FORMAT( RecentMutations.`Date` , '%Y-%M') = DATE_FORMAT( NOW() , '%Y-%M');
6.查询本周的数据:
SELECT * FROM RecentMutations WHERE YEARWEEK( RecentMutations.`Date`) = YEARWEEK( NOW() );
7.查询上周的数据:
SELECT * FROM RecentMutations WHERE YEARWEEK( RecentMutations.`Date`) = YEARWEEK( NOW() ) - 1;