mysql分组查询时间区间内每天的COUNT

最新推荐文章于 2024-05-01 08:00:00 发布

原创最新推荐文章于 2024-05-01 08:00:00 发布 · 501 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #sql

mysql优化专栏收录该内容

5 篇文章

订阅专栏

本文档展示了如何使用SQL查询从日期序列中筛选特定区间（2022-10-13到2022-10-19），并统计与bm_warning_message表中未删除消息的每日计数，结合日期格式化和左连接操作。

直接上代码，一点不犹豫

select a.Date, case when b.count then b.count else 0 end
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
left join (
	SELECT
	date_format( create_time, '%Y-%m-%d' ) Date,
	count(*) as count
FROM
	bm_warning_message 
WHERE
	is_deleted = FALSE AND create_time BETWEEN '2022-10-11' and '2022-10-31'
GROUP BY
	date_format( create_time, '%Y-%m-%d' ) 
ORDER BY
	create_time ASC
) b on a.Date = b.Date
where a.Date between '2022-10-13' and '2022-10-19' order by a.Date asc