mysql 半年统计 前6个月 的统计 没有补0
select if(s.count is NULL, 0,s.count) as count ,t.time from ( select count(1) as count, date_format( event_time, '%Y-%m') as tname from sys_event e LEFT JOIN sys_applets a on e.openid=a.openid where event_time between date_sub(now(),interval 6 month) and now() AND a.position_type='0' GROUP BY date_format( event_time, '%Y-%m') ) as s RIGHT JOIN (select date_format(date_add(now(),interval -5 month),'%Y-%m') as time union all select date_format(date_add(now(),interval -4 month),'%Y-%m') union all select date_format(date_add(now(),interval -3 month),'%Y-%m') union all select date_format(date_add(now(),interval -2 month),'%Y-%m') union all select date_format(date_add(now(),interval -1 month),'%Y-%m') union all select date_format(date_add(now(),interval -0 month),'%Y-%m') ) as t ON s.tname=t.time ORDER BY t.time asc1 、 查看当天日期
select current_date(); 1 2、 查看当天时间
select current_time(); 1 3、查看当天时间日期
select current_timestamp(); 1 4、查询当天记录
select * from 表名 where to_days(时间字段名) = to_days(now()); 1 5、查询昨天记录
select * from 表名 where to_days( now( ) ) – to_days( 时间字段名) <= 1 1 6、查询7天的记录
select * from 表名 where date_sub(curdate(), interval 7 day) <= date(时间字段名) 1 7、查询近30天的记录
select * from 表名 where date_sub(curdate(), interval 30 day) <= date(时间字段名) 1 8、查询本月的记录
select * from 表名 where date_format( 时间字段名, ‘%y%m’ ) = date_format( curdate( ) , ‘%y%m’ ) 1 9、查询上一月的记录
select * from 表名 where period_diff( date_format( now( ) , ‘%y%m’ ) , date_format( 时间字段名, ‘%y%m’ ) ) =1 1 10、查询本季度数据
select * from 表名 where quarter(create_date)=quarter(now()); 1 11、查询上季度数据
select * from 表名 where quarter(create_date)=quarter(date_sub(now(),interval 1 quarter)); 1 12、查询本年数据
select * from 表名 where year(create_date)=year(now()); 1 13、查询上年数据
select * from 表名 where year(create_date)=year(date_sub(now(),interval 1 year)); 1 14、查询当前这周的数据
select * from 表名 where yearweek(date_format(submittime,’%y-%m-%d’)) = yearweek(now()); 1 15、查询上周的数据
select * from 表名 where yearweek(date_format(submittime,’%y-%m-%d’)) = yearweek(now())-1; 1 16、查询当前月份的数据
select * from 表名 where date_format(submittime,’%y-%m’)=date_format(now(),’%y-%m’) 1 17、查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
18最近七天 没有补0
select DATE_FORMAT(mycreatedate,'%Y-%m-%d') as name,ifnull(COUNT(pv.event_time), 0) as count from ( SELECT curdate() as mycreatedate union all SELECT date_sub(curdate(), interval 1 day) as mycreatedate union all SELECT date_sub(curdate(), interval 2 day) as mycreatedate union all SELECT date_sub(curdate(), interval 3 day) as mycreatedate union all SELECT date_sub(curdate(), interval 4 day) as mycreatedate union all SELECT date_sub(curdate(), interval 5 day) as mycreatedate union all SELECT date_sub(curdate(), interval 6 day) as mycreatedate ) as total LEFT JOIN (select e.event_time from sys_event e LEFT JOIN sys_applets a on e.openid=a.openid where a.position_type='3') pv on DATE_FORMAT(pv.event_time,'%Y-%m-%d')=total.mycreatedate GROUP BY mycreatedate ORDER BY mycreatedate asc