mysql根据时间段生成时间

在开发过程中，经常会遇到统计，如果统计的时间段内有间隔，不管是左连接还是右连接都不会有，所以这时候我们需要写个语句能补全这段时间内所有的时间，然后用时间去关联业务查询。

-- 本周
select DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) DAY) as firstDay,
			CURDATE() + INTERVAL(6 - WEEKDAY(CURDATE())) DAY as lastDay

-- 本月
select DATE_FORMAT(LAST_DAY(CURDATE()),'%Y-%m-01 00:00:00') as firstDay,
			DATE_FORMAT(LAST_DAY(CURDATE()),'%Y-%m-%d 23:59:59') as lastDay

-- 本年
select DATE_FORMAT(MAKEDATE(YEAR(NOW()),1),'%Y-%m-%d 00:00:00') as firstDay,
			CONCAT(YEAR(NOW()),'-12-31 23:59:59') as lastDay

-- 生成指定时间段内的所有日期
select @num := @num +1,date_format(adddate(DATE_SUB('2024-03-04',interval 1 Day ),interval @num Day),'%Y-%m-%d') as date
from account_user,(select @num :=0) t
where adddate('2024-03-04',interval @num Day) <= date_format('2024-04-10','%Y-%m-%d') order by date;

-- 生成一天24小时
select @num := @num +1,date_format(adddate(DATE_SUB('2024-03-12 00:00:00',interval 1 HOUR ),interval @num HOUR),'%H') as date
from account_user,(select @num :=0) t
where @num <=23;

-- 生成一天24小时带分
select @num := @num +1,date_format(adddate(DATE_SUB('2024-03-12 00:00:00',interval 1 MINUTE ),interval @num MINUTE),'%H:%i') as date
from health_job,(select @num :=0) t
where @num <=1439
-- 递归查询下级
WITH recursive subordinates as (
select company_id,`name` from account_company where company_id in <foreach collection="list" item="item" open="(" separator="," close=")">#{item}</foreach>
UNION ALL
select t.company_id,t.`name` from account_company t JOIN subordinates s on t.parent_id = s.company_id
)
select DISTINCT company_id from subordinates

其中 2023-01-01 是开始时间，2024-04-01是结束时间，这样就会生成从2023-01-01 到 2024-04-01所有日期，然后用生成的这些日期去关联业务表做统计查询。