假设起止时间分别为P_STARTTIME P_ENDTIME
试一下吧
SELECT P_ENDTIME-P_STARTTIME-SUM(LEAST(E,P_ENDTIME)-GREATEST(S,P_STARTTIME))
FROM(
select TRUNC(P_STARTTIME)+rownum-1+12/24 S,
TRUNC(P_STARTTIME)+rownum-1+13.5/24 E
from dual connect by rownum<=TRUNC(P_ENDTIME)-TRUNC(P_STARTTIME)+1
)WHERE S<=P_ENDTIME AND E>=P_STARTTIME
试一下吧
SELECT P_ENDTIME-P_STARTTIME-SUM(LEAST(E,P_ENDTIME)-GREATEST(S,P_STARTTIME))
FROM(
select TRUNC(P_STARTTIME)+rownum-1+12/24 S,
TRUNC(P_STARTTIME)+rownum-1+13.5/24 E
from dual connect by rownum<=TRUNC(P_ENDTIME)-TRUNC(P_STARTTIME)+1
)WHERE S<=P_ENDTIME AND E>=P_STARTTIME
SELECT P_ENDTIME-P_STARTTIME-NVL(SUM(LEAST(E,P_ENDTIME)-GREATEST(S,P_STARTTIME)),0)
FROM(
select TRUNC(P_STARTTIME)+rownum-1+12/24 S,
TRUNC(P_STARTTIME)+rownum-1+13.5/24 E
from dual connect by rownum<=TRUNC(P_ENDTIME)-TRUNC(P_STARTTIME)+1
)WHERE S<=P_ENDTIME AND E>=P_STARTTIME
when to_char(start_time,'HH24MI')<='1330' then 10.5*60 else (trunc(start_time+1)-start_time)*1440 end+case when to_char(end_time)>='1330' then (end_time-trunc(end_time))*1440-1.5*60
when to_char(end_time)>='1200' then 720 else (end_time-trunc(end_time))*1440