Mysq 查询每天的某情况统计总数

最新推荐文章于 2024-07-21 03:10:10 发布

不正经写文章的我

最新推荐文章于 2024-07-21 03:10:10 发布

阅读量158

点赞数 2

文章标签：数据库 sql mysql 每日统计

本文链接：https://blog.csdn.net/chechenshi/article/details/140492798

版权

-- 假设今天是 2024-07-09，查询出 7 条 2024-07-10 的数据，这里 table_1 可以是任意表
SELECT @tempdate := DATE_ADD( CURDATE(), INTERVAL 1 DAY ) FROM table_1 LIMIT 7

-- 查询出过去 7 天的时间
SELECT
		DATE_FORMAT( @tempdate := DATE_SUB( @tempdate, INTERVAL 1 DAY ), '%%Y-%%m-%%d' ) AS date 
	FROM
		( SELECT @tempdate := DATE_ADD( CURDATE(), INTERVAL 1 DAY ) FROM table_1 LIMIT %d ) d

-- 查询出过去 7 天的每天异常数据的总数
SELECT count(*) as total, DATE_FORMAT(abnormal_date, '%%Y-%%m-%%d') as date 
FROM table_1
WHERE abnormal_date >= '2024-06-17 00:00:00' and abnormal_date <= '2024-06-21 00:00:00'
GROUP BY DATE_FORMAT(abnormal_date, '%%Y-%%m-%%d')

-- left join 查询出过去 7 天的异常数据, 空的话显示 0
SELECT t1.date date, IFNULL(t2.total, 0) total
FROM (
	SELECT
		DATE_FORMAT( @tempdate := DATE_SUB( @tempdate, INTERVAL 1 DAY ), '%%Y-%%m-%%d' ) AS date 
	FROM
		( SELECT @tempdate := DATE_ADD( CURDATE(), INTERVAL 1 DAY ) FROM table_1 LIMIT 7 ) d) as t1
LEFT JOIN (
	SELECT count(*) as total, DATE_FORMAT(abnormal_date, '%%Y-%%m-%%d') as date 
	FROM table_1
	WHERE abnormal_date >= '2024-06-17 00:00:00' and abnormal_date <= '2024-06-21 00:00:00'
	GROUP BY DATE_FORMAT(abnormal_date, '%%Y-%%m-%%d')
) t2
ON t1.date = t2.date
ORDER BY t1.date ASC