下面展示一些 内联代码片
。
//sql查询某一天, 某一月, 某一年的数据
// 查询今日增加数量
SELECT COUNT(id) FROM sensordata WHERE TO_DAYS(insertTime)=TO_DAYS(now());
//查询昨日日增加数量
SELECT COUNT(id) FROM sensordata WHERE DATEDIFF(insertTime,NOW())=-1
//查询今年日增加数量
select COUNT(id) from sensordata where YEAR(insertTime)=YEAR(NOW());
//查询去年增长数量
select COUNT(id) from sensordata where year(insertTime)=year(date_sub(now(),interval 1 year));
//查询当前这周的数据(周日开始)
SELECT * FROM sensordata WHERE YEARWEEK(date_format(insertTime,'%Y-%m-%d')) = YEARWEEK(now()) and year(insertTime)=YEAR(NOW());
//查询当前这周的数据(周一开始)
SELECT * FROM sensordata WHERE YEARWEEK(date_format(insertTime,'%Y-%m-%d'),1) = YEARWEEK(now(),1) and year(insertTime)=YEAR(NOW());
//查询上周的数据(周日开始)
SELECT *FROM sensordata WHERE YEARWEEK(date_format(insertTime,'%Y-%m-%d')) = YEARWEEK(now())-1 and year(insertTime)=YEAR(NOW());
//查询上周的数据(周一开始)
SELECT *FROM sensordata WHERE YEARWEEK(date_format(insertTime,'%Y-%m-%d'),1) = YEARWEEK(now(),1)-1 and year(insertTime)=YEAR(NOW());
//查询本季度数据
select * from sensordata where QUARTER(insertTime)=QUARTER(now()) and year(insertTime)=YEAR(NOW());
//查询上季度数据
select * from sensordata where QUARTER(insertTime)=QUARTER(DATE_SUB(now(),interval 1 QUARTER)) and year(insertTime)=YEAR(NOW());
//查询前6个月的数据
select * from sensordata where insertTime between date_sub(now(),interval 6 month) and now();
总结
以上所述是小编自己在项目中遇到记录下MySQL中查询某一天, 某一月, 某一年的数据代码详解,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对我们网站的支持!
如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!
本文标题: MySQL中查询某一天, 某一月, 某一年的数据代码详解
本文地址: http://www.cppcns.com/shujuku/mysql/262002.html