mysql根据开始和结束日期统计每天个数

最新推荐文章于 2023-09-19 17:04:16 发布

q_q_hh

最新推荐文章于 2023-09-19 17:04:16 发布

阅读量805

点赞数 1

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_21298819/article/details/129147682

版权

有一个表有开始日期start_date和结束日期end_date，现在需要统计2.16-2.21每天在这个时间段的数量：如：想的到如下结果

2.16 – 2(在3，5日期范围内)
2.17 – 3(在2，3，5日期范围内)
2.18 – 5
2.19 – 5
2.20 – 4
2.21 – 3

在这里插入图片描述

SELECT a.fd_time
,COUNT(b.id) AS fd_value_plan
FROM (
	select fd_time from
	(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS fd_time from
	 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
	 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
	 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
	 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
	 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
	where fd_time between date_sub( CURDATE(), INTERVAL 5 DAY ) and CURDATE() 
	ORDER BY fd_time
	) a
	LEFT JOIN 
	( SELECT id,start_date,end_date FROM test ) b
	ON a.fd_time BETWEEN b.start_date and b.end_date
GROUP BY a.fd_time