SQL题目6：关于传入时间段求工作时间的题目

with t_x as  ---用户参数输入
(select &输入开始时间 as start_time ,&输入结束时间 as end_time from dual)
,
t10 as     ---用于对重复数据去重和对最后无out数据的容错
(select distinct id,gid,activity,txntimestamp
from system.aareport  union all
select distinct id,gid,'out' as  activity,date'4712-12-31' as txntimestamp
from system.aareport
)
,t2 as   --- 对数据进行编号
(
select  id,gid,activity,txntimestamp,
row_number()over(partition by id,gid order by txntimestamp) as flag
from t10
)
-----将开始时间和结束时间写在同一行中，并对实际时间和传入时间进行对比选取
-----最后计算总时间的和
select t3.id,t3.gid,min(greatest(t3.txntimestamp,t5.start_time)) as start_time ,
max(least(t4.txntimestamp,t5.end_time)) as end_time ,
sum(least(t4.txntimestamp,t5.end_time)-
greatest(t3.txntimestamp,t5.start_time))*24 as sum_time
from t2 t3 , t2 t4,t_x t5
where t3.id=t4.id and t3.gid =t4.gid and
t3.flag=t4.flag-1  and t3.txntimestamp <=t5.end_time
and t4.txntimestamp >=t5.start_time
and t3.activity ='in'
and t4.activity ='out'
group by t3.id  ,t3.gid