[人工智能]人脸识别记录查询当月每天统计的记录总数没有则补0

当月

SET @i :=- 1;
SELECT
	x.DAY,
	IFNULL( d.total, 0 ) AS total 
FROM
	(  --该段
	SELECT  
		date_add(
			CONCAT( YEAR ( Date( curdate())), '-0', MONTH ( Date( curdate())), '-', '01' ),
			INTERVAL ( cast( help_topic_id AS signed INTEGER ) ) DAY 
		) DAY 
	FROM
		mysql.help_topic 
	WHERE
		help_topic_id < DAY ( last_day( curdate( ) ) ) 
	ORDER BY
		help_topic_id 
	) x  --可替换为近30天的生成日期方法，如下所示
	LEFT JOIN (
	SELECT
		DATE( fir.identify_time ) AS create_time,
		count( fir.identify_record ) AS total 
	FROM
		face_identify_record fir 
	GROUP BY
		DATE( fir.identify_time ) 
	) d ON TO_DAYS( x.DAY ) = TO_DAYS( DATE( d.create_time ) ) 
ORDER BY
	x.DAY

近30天

SET @i :=- 1;
SELECT
	x.`time`,
-- 	IFNULL( d.create_time, x.`time` ) AS `create_time`,
	IFNULL( d.total, 0 ) AS total
FROM
	(
	SELECT
		date_format( DATE_SUB( NOW( ), INTERVAL ( @i := @i + 1 ) DAY ), '%Y-%m-%d' ) AS `time` 
	FROM
		(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1, 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2
	WHERE
		@i <= 30 
	) x
	LEFT JOIN 
	(
		SELECT
		DATE( fir.identify_time ) AS create_time,
		count( fir.identify_record ) AS total 
	FROM
		face_identify_record fir 
	GROUP BY
		DATE( fir.identify_time )
	) d ON TO_DAYS( x.`time` ) = TO_DAYS( DATE( d.create_time ) ) 
ORDER BY
	x.`time`

1. NOW() 可以替换成你想要的任意时间；
2. DATE_SUB 表示从 NOW() 时间起往前多少天的连续日期，如果需要往后的连续日期，改为 DATE_ADD 即可。
3. 在获得连续日期的语句中，WHERE 条件中的 @i <= 30 表示连续 30 天的日期，数值可自定义，选择自己想要查询的连续时间范围。