在开发过程中,经常会遇到统计,如果统计的时间段内有间隔,不管是左连接还是右连接都不会有,所以这时候我们需要写个语句能补全这段时间内所有的时间,然后用时间去关联业务查询。
-- 本周
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所有日期,然后用生成的这些日期去关联业务表做统计查询。