SQL 计算起止时间内开关机时间

发布网友发布时间：2022-04-11 01:35

共2个回答

热心网友时间：2022-04-11 03:04

sql server的，没有ORACLE环境，你自己转成ORACLE的吧：
select A.dtime as '开机时间'
,B.dtime as '关机时间'
,(convert(varchar,datepart(HH,B.dtime)-(datepart(HH,A.dtime)))+'时'
+convert(varchar,(datepart(MI,B.dtime)-datepart(MI,A.dtime)))+'分'
+convert(varchar,(datepart(S,B.dtime)-datepart(S,A.dtime)))+'秒') AS '开机耗时'

from
(select 时间点 as dtime,
ROW_NUMBER() OVER(order by 时间点 asc) as rownum
from dd
where 类型='A'
) A
left join
(select 时间点 as dtime,
ROW_NUMBER() OVER(order by 时间点 asc) as rownum
from dd
where 类型='B'
)B
on A.rownum = B.rownum

热心网友时间：2022-04-11 04:22

step1 列出开关机的对应关系：

select
t1.shijian as 开机时间点,
(
select min(t2.shijian) from tbl_test t2 where 1=1
and t2.leixing = 'B'
and t2.shijian > t1.shijian
) as 关机时间点
from tbl_test t1
where 1=1
and t1.leixing = 'A'

step2 做减法：

select
(
select min(t2.shijian) from tbl_test t2 where 1=1
and t2.leixing = 'B'
and t2.shijian > t1.shijian
) - t1.shijian as 开机时长
from tbl_test t1
where 1=1
and t1.leixing = 'A'

step3 将减法结果做汇总（sum）：

不再赘述。

SQL 计算起止时间内 开关机时间

SQL 计算起止时间内开关机时间