mysql查询时间区间的数据统计

1、按天统计，统计区间的所有天数的数据，没有的赋值为0 

SELECT
	DATE_FORMAT( a.date, '%Y-%m-%d' ) AS abscissa_name,
	IFNULL( b.count, 0 ) AS total 
FROM
	(
SELECT
	@num := @num + 1 AS num,
	date_format( adddate( date_sub( '2021-11-03', INTERVAL 1 DAY ), INTERVAL @num DAY ), '%Y-%m-%d' ) AS date 
FROM
	rd_resource_application_detail,
	( SELECT @num := 0 ) t 
WHERE
	adddate( date_sub( '2021-11-03', INTERVAL 1 DAY ), INTERVAL @num DAY ) < date_format( '2021-11-09', '%Y-%m-%d' ) 
ORDER BY
	date 
	) a
	LEFT JOIN (
SELECT
	count( * ) AS count,
	DATE_FORMAT( apply_time, '%Y-%m-%d' ) AS date 
FROM
	`rd_resource_application_detail` 
WHERE
DATE_FORMAT( apply_time, '%Y-%m-%d' ) BETWEEN '2021-11-03' AND '2021-11-09' 
GROUP BY
	date 
	) b ON a.date = b.date 
ORDER BY
	a.date ASC

2、统计时间区间的数据，按月统计每月的数据，没有的赋值为0 

SELECT
	a.abscissa_name,
	sum( count ) AS total 
FROM
	(
SELECT
	* 
FROM
	(
SELECT
	count( t.id ) AS count,
	date_format( t.apply_time, '%Y-%m' ) AS abscissa_name 
FROM
	rd_resource_application_detail t 
WHERE
	t.state = 6 
	AND DATE_FORMAT( t.apply_time, '%Y-%m-%d' ) BETWEEN '2020-01-01' AND '2021-12-31' 
GROUP BY
	DATE_FORMAT( t.apply_time, '%Y-%m' ) UNION ALL
SELECT
	@uu := 0 AS count,
	abscissa_name 
FROM
	(
SELECT
	@num := @num + 1 AS number,
	date_format( adddate( '2020-01-01', INTERVAL @num MONTH ), '%Y-%m' ) AS abscissa_name 
FROM
	rd_resource_application_detail a,
	( SELECT @num := - 1 ) t 
WHERE
	adddate( '2020-01-01', INTERVAL @num MONTH ) < adddate( '2021-12-31', INTERVAL - 1 MONTH ) 
ORDER BY
	abscissa_name 
	) rr 
	) tt 
ORDER BY
	tt.abscissa_name 
	) a 
GROUP BY
	a.abscissa_name;

3、统计某天的24小时的数据，没有的赋值为0 

SELECT
	a.HOUR,
	concat( IF ( a.HOUR < 10, concat( '0', a.HOUR ), a.HOUR ), ':00' ) AS abscissa_name,
	ifnull( b.count, 0 ) AS total 
FROM
	(
SELECT
	0 AS HOUR UNION ALL
SELECT
	1 AS HOUR UNION ALL
SELECT
	2 AS HOUR UNION ALL
SELECT
	3 AS HOUR UNION ALL
SELECT
	4 AS HOUR UNION ALL
SELECT
	5 AS HOUR UNION ALL
SELECT
	6 AS HOUR UNION ALL
SELECT
	7 AS HOUR UNION ALL
SELECT
	8 AS HOUR UNION ALL
SELECT
	9 AS HOUR UNION ALL
SELECT
	10 AS HOUR UNION ALL
SELECT
	11 AS HOUR UNION ALL
SELECT
	12 AS HOUR UNION ALL
SELECT
	13 AS HOUR UNION ALL
SELECT
	14 AS HOUR UNION ALL
SELECT
	15 AS HOUR UNION ALL
SELECT
	16 AS HOUR UNION ALL
SELECT
	17 AS HOUR UNION ALL
SELECT
	18 AS HOUR UNION ALL
SELECT
	19 AS HOUR UNION ALL
SELECT
	20 AS HOUR UNION ALL
SELECT
	21 AS HOUR UNION ALL
SELECT
	22 AS HOUR UNION ALL
SELECT
	23 AS HOUR 
	) a
	LEFT JOIN (
SELECT HOUR
	( apply_time ) AS HOUR,
	count( apply_time ) AS count 
FROM
	rd_resource_application_detail 
WHERE
	state = 6 
	AND DATE_FORMAT( apply_time, '%Y-%m-%d' ) = '2021-01-01' 
GROUP BY
	date_format( apply_time, '%Y-%m-%d %h' ),
HOUR 
	) b ON a.HOUR = b.HOUR 
ORDER BY
	HOUR ASC;

 

4、统计近多少天的数据，通过limt获取多少天的统计数据  LIMIT 15 ，15就是近多少天的数据

SELECT
	t1.`day`,
	t1.`day` as abscissa_name,
	DATE_FORMAT(t1.`day`,"%Y-%m") as dayes,
	COUNT( t2.id ) total 
FROM
	(
SELECT
	@cdate := DATE_ADD( @cdate, INTERVAL - 1 DAY ) DAY 
FROM
	( SELECT @cdate := DATE_ADD( '2021-11-05', INTERVAL + 1 DAY ) FROM rd_resource_application_detail ) t0 
	LIMIT 15
	) t1
	LEFT JOIN (
SELECT
	DATE( a.apply_time ) DAY,
	a.id 
FROM
	rd_resource_application_detail a 
WHERE a.state = 6 and 
	DATE(a.apply_time) <= '2021-11-05' AND DATE(a.apply_time) > DATE_SUB( '2021-11-04', INTERVAL 1 DAY ) 
	) t2 ON t2.DAY = t1.DAY 
GROUP BY t1.`day`;

5、统计近几天的总记录

select * from `article` where date_sub(curdate(), INTERVAL 7 DAY) <= date(`add_time`);

select * from `表名` where date_sub(curdate(), INTERVAL 天数 DAY) <= date(`add_time`);

6、昨天、本月、本年

昨日
select * from `表名` where to_days(now()) – to_days(`add_time`) <= 1;

本月
select * from `表名` where date_format(`add_time`, ‘%Y%m') = date_format(curdate() , ‘%Y%m');

上一月
select * from `表名` where period_diff(date_format(now() , ‘%Y%m') , date_format(`add_time`, ‘%Y%m')) =1;


本年
第一种写法：SELECT * FROM `rd_resource_application_detail` WHERE  DATE_FORMAT(apply_time,"%Y") =  YEAR(NOW());

第二种写法：SELECT * FROM `rd_resource_application_detail` WHERE  DATE_FORMAT(apply_time,"%Y") =  DATE_FORMAT(NOW(),"%Y");

前几年的数据
去年：SELECT * FROM `rd_resource_application_detail` WHERE  DATE_FORMAT(apply_time,"%Y") = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 YEAR),"%Y")

前年：SELECT * FROM `rd_resource_application_detail` WHERE  DATE_FORMAT(apply_time,"%Y") = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 2 YEAR),"%Y")

7、统计时间区间日期列表

SELECT
	@num := @num + 1 AS num,
	date_format( adddate( date_sub( '2021-10-03', INTERVAL 1 DAY ), INTERVAL @num DAY ), '%Y-%m-%d' ) AS date 
FROM
	realm_main_indicators_data,
	( SELECT @num := 0 ) t 
WHERE
	adddate( date_sub( '2021-10-03', INTERVAL 1 DAY ), INTERVAL @num DAY ) < date_format( '2021-11-09', '%Y-%m-%d' ) 
ORDER BY
	date