mysql 每周 每天 季度 半年统计 前6个月 的统计 没有补0

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 asc

1 、 查看当天日期

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