select
case when tb.设备ID=5005 then '1#'
end as 设备号,
round(cast((ta.done)as float)/tb.待机时间,2)as 能率,
tb.日期 as 日期
from
(
select
count(file_name) as done,
convert(varchar(100),end_date,111) as end_date
from iot_t_kadoudata_knc
where 1=1
and machine_id=5005
and end_date between convert(varchar(100),dateadd(day,-6,dateadd(day,-1,getdate())),111)+' 08:00:00' and convert(varchar(100),dateadd(day,-1,getdate()),111)+' 19:00:00'
group by convert(varchar(100),end_date,111)
)ta,
(
select
a.日期,
a.设备ID,
round(cast((a.待机时间)as float)/60,2)as 待机时间
from iot_v_kadoudata_latest_7_days a
where 1=1
and a.设备ID=5005
group by a.日期,a.设备ID,a.待机时间
)tb运行上边的代码得到的结果如图：过去7天 每天的done结果如图：过去7天每天待机时间的结果如图：
