-- 假设今天是 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
Mysq 查询每天的某情况统计总数
最新推荐文章于 2024-07-21 03:10:10 发布