mysql每隔45分钟分组查找时间段内最大与最小数据的差值
关键点在于case 先让 子查询 在确定时间后执行不然必失败为null,
子查询的优先度为第二,第一是from后的基表,然后是子查询然后是字段
要子查询在字段后执行使用case延后子查询的优先度
set @dates := DATE_sub(date_format('2021-12-01 17:15:00', '%Y-%m-%d 00:00'), interval 45 minute);
set @dates1:= date_format('2021-12-01 17:15:00', '%Y-%m-%d 00:00');
select allop.time,allop.nums from(
select tc22.tc1 as time,tc22.tc2,(case when
tc1!=tc2
then
(select uio from
(select ifnull(max(code_05),0)-if((min(code_05)is null) or min(code_05)=0,ifnull(max(code_05),0),min(code_05)) as uio
from zkyq_efe.ele_enterprise_data where enterprise_id = '302373134751760384'
and data_time>=
tc1
and data_time <=
tc2
order by code_05 desc
limit 1
) as tccc) else tc1 end)as nums
from (select (@dates := DATE_ADD(@dates, interval 45 minute)) as tc1,
(@dates1:= DATE_ADD(@dates1, interval 45 minute)) as tc2
from zkyq_efe.ele_enterprise_data
limit 32) as tc22 )as allop