1.按照年份月份拆分字段,并统计。
SELECT DATE_FORMAT(creat_time,"%Y年%m月") AS dates,COUNT(*) FROM ssoml.mlcp_node GROUP BY
DATE_FORMAT(creat_time,"%Y年%m月")
ORDER BY DATE_FORMAT(creat_time,"%Y年%m月") DESC ;
如图:
2.如上图表:
问题:我要求每个月新增了几条数据,截止当前月份共有几条数据
select tt.dates AS 时间,tt.counts AS 本月新增,
(select count(*) from mlcp_node where creat_time<=a.creat_time- interval 0 day) as 截止本月前共有实验
from mlcp_node a ,
((SELECT DATE_FORMAT(creat_time,'%Y年%m月') AS dates,COUNT(*) AS counts FROM ssoml.mlcp_node GROUP BY
DATE_FORMAT(creat_time,'%Y年%m月')
ORDER BY DATE_FORMAT(creat_time,'%Y年%m月') DESC)) tt where DATE_FORMAT(a.creat_time,'%Y年%m月')=tt.dates
GROUP BY tt.dates
ORDER BY creat_time DESC;
3.(该条为转载 链接 https://blog.csdn.net/chengzhewang/article/details/42918703)
查询今天的记录:
select * from story where to_days(create_time) = to_days(now());
查询昨天的记录:
select * from story where to_days(now()) - to_days(create_time) = 1;
查询近7天的信息记录:
select * from story where DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= DATE(create_time);
查询近30天的信息记录:
select * from story where DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DATE(create_time);
查询本月的信息记录:
select * from story where DATE_FORMAT(create_time,'%Y-%m') = DATE_FORMAT(CURDATE(),'%Y-%m');
查询上月的信息记录:
select * from story where PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y-%M'),DATE_FORMAT(create_time,'%Y-%m')) = 1;